package com.ufo.store.management.database;

import android.content.ContentValues;
import android.content.Context;
import android.text.TextUtils;

import com.ufo.store.management.BarApplication;
import com.ufo.store.management.entity.Isszmbalv3BranchInfo;
import com.ufo.store.management.entity.Isszmbalv3Member;
import com.ufo.store.management.entity.Isszmbalv3MemberPointInfo;
import com.ufo.store.management.entity.Isszmbalv3PayFlow;
import com.ufo.store.management.entity.Isszmbalv3SaleDetailInfo;
import com.ufo.store.management.entity.Isszmbalv3StockInfo;
import com.ufo.store.management.entity.Member;
import com.ufo.store.management.entity.MemberPointInfo;
import com.ufo.store.management.entity.PayMaster;
import com.ufo.store.management.entity.SaleDetailInfo;
import com.ufo.store.management.entity.StockInfo;
import com.ufo.store.management.entity.Isszmbalv3SupcustInfo;
import com.ufo.store.management.utils.Utils;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.TreeMap;

/**
 * Created by Administrator on 2015/9/23 0023.
 */
public class MicrosoftSQLServerHelper {
    private Context mContext = null;
    private static MicrosoftSQLServerHelper sInstance = null;

    public MicrosoftSQLServerHelper(Context context) {
        this.mContext = context;
    }

    public static synchronized MicrosoftSQLServerHelper getInstance(Context context) {
        if (sInstance == null)
            sInstance = new MicrosoftSQLServerHelper(context);
        return sInstance;
    }

    public long searchById(long maxIndex) throws Exception {
        // 加载驱动程序
        Class.forName("net.sourceforge.jtds.jdbc.Driver");
        Connection con = DriverManager.getConnection("jdbc:jtds:sqlserver://" + BarApplication.getInstance().mSqlServerHost + "/fastfood", BarApplication.getInstance().mSqlServerUser, BarApplication.getInstance().mSqlServerPassword);
        long maxIndexDst = maxIndex + 1;
        long recordCount = 1;
        while (recordCount >= 1) {
            String sql = "SELECT " + DatabaseManager.STOCK_STOCKID + " FROM " + DatabaseManager.STOCK_TABLE_NAME + " WHERE " + DatabaseManager.STOCK_STOCKID + "=" + maxIndexDst;
            Statement stmt = con.createStatement();//创建Statement
            ResultSet rs = stmt.executeQuery(sql);//ResultSet类似Cursor
            if (rs.next()) {
                recordCount = rs.getInt(1);
            } else {
                recordCount = 0;
            }
            if (recordCount >= 1) {
                maxIndexDst++;
            }
            rs.close();
            stmt.close();
        }
        if (con != null)
            con.close();
        return maxIndexDst;
    }

    public int getStockCount(String stockTypeId) throws Exception {
        // 加载驱动程序
        Class.forName("net.sourceforge.jtds.jdbc.Driver");
        Connection con = DriverManager.getConnection("jdbc:jtds:sqlserver://" + BarApplication.getInstance().mSqlServerHost + "/fastfood", BarApplication.getInstance().mSqlServerUser, BarApplication.getInstance().mSqlServerPassword);
        String sql = "";
        if (!stockTypeId.equals("-1"))
            sql = "select count(1) as totalCount from " + DatabaseManager.STOCK_TABLE_NAME + " WHERE " + DatabaseManager.STOCK_TYPEID + "=" + stockTypeId;
        else
            sql = "select count(1) as totalCount from " + DatabaseManager.STOCK_TABLE_NAME;
        int totalCount = -1;
        try {
            Statement stmt = con.createStatement();//创建Statement
            ResultSet rs = stmt.executeQuery(sql);//ResultSet类似Cursor
            if (rs.next()) {
                totalCount = rs.getInt(1);
            }
            stmt.close();
        } catch (Exception e) {
            e.printStackTrace();
        }

        if (con != null)
            con.close();
        return totalCount;
    }

    private List<StockInfo> collectStockInfoList(ResultSet rs) throws Exception {
        List<StockInfo> result = new ArrayList<StockInfo>();
        if (rs != null) {
            while (rs.next()) {
                StockInfo stockInfo = new StockInfo();
                stockInfo.stockId = rs.getString(DatabaseManager.STOCK_STOCKID).trim();
                stockInfo.stockName = rs.getString(DatabaseManager.STOCK_STOCKNAME);
                stockInfo.stockUnit = rs.getString(DatabaseManager.STOCK_UNIT).trim();
                stockInfo.stockTpyeId = rs.getString(DatabaseManager.STOCK_TYPEID);
                stockInfo.stockCost = rs.getFloat(DatabaseManager.STOCK_COST);
                stockInfo.stockDetailPrice = rs.getFloat(DatabaseManager.STOCK_RETAIL_PRICE);
                stockInfo.stockMemberPrice = rs.getFloat(DatabaseManager.STOCK_MEMBER_PRICE);
                stockInfo.stockCanDiscount = rs.getString(DatabaseManager.STOCK_CAN_DISCOUNT);
                stockInfo.stockBarcode = rs.getString(DatabaseManager.STOCK_BARCODE);
                stockInfo.stockSpell = rs.getString(DatabaseManager.STOCK_SPELL);
                result.add(stockInfo);
            }
            rs.close();
        }
        return result;
    }

    public List<StockInfo> getAll(int startIndex, int pageSize, int maxCount, int sortMode) throws Exception {
        // 加载驱动程序
        Class.forName("net.sourceforge.jtds.jdbc.Driver");
        Connection con = DriverManager.getConnection("jdbc:jtds:sqlserver://" + BarApplication.getInstance().mSqlServerHost + "/fastfood", BarApplication.getInstance().mSqlServerUser, BarApplication.getInstance().mSqlServerPassword);
        String sortName = DatabaseManager.STOCK_STOCKID;
        String sortUpOrDown0 = " ASC ";
        String sortUpOrDown1 = " DESC ";
        switch (sortMode) {
            case 0:
                sortName = DatabaseManager.STOCK_SPELL;
                sortUpOrDown0 = " ASC ";
                sortUpOrDown1 = " DESC ";
                break;
            case 1:
                sortName = DatabaseManager.STOCK_SPELL;
                sortUpOrDown0 = " DESC ";
                sortUpOrDown1 = " ASC ";
                break;
            case 2:
                sortName = DatabaseManager.STOCK_STOCKID;
                sortUpOrDown0 = " ASC ";
                sortUpOrDown1 = " DESC ";
                break;
            case 3:
                sortName = DatabaseManager.STOCK_STOCKID;
                sortUpOrDown0 = " DESC ";
                sortUpOrDown1 = " ASC ";
                break;
            case 4:
                sortName = DatabaseManager.STOCK_RETAIL_PRICE;
                sortUpOrDown0 = " ASC ";
                sortUpOrDown1 = " DESC ";
                break;
            case 5:
                sortName = DatabaseManager.STOCK_RETAIL_PRICE;
                sortUpOrDown0 = " DESC ";
                sortUpOrDown1 = " ASC ";
                break;
        }
        String sql = "";
        if (pageSize == -1) {
            sql = "SELECT " + DatabaseManager.STOCK_STOCKID + "," +
                    DatabaseManager.STOCK_STOCKNAME + "," +
                    DatabaseManager.STOCK_UNIT + "," +
                    DatabaseManager.STOCK_TYPEID + "," +
                    DatabaseManager.STOCK_COST + "," +
                    DatabaseManager.STOCK_RETAIL_PRICE + "," +
                    DatabaseManager.STOCK_MEMBER_PRICE + "," +
                    DatabaseManager.STOCK_CAN_DISCOUNT + "," +
                    DatabaseManager.STOCK_BARCODE + "," +
                    DatabaseManager.STOCK_SPELL +
                    " FROM " + DatabaseManager.STOCK_TABLE_NAME + " ORDER BY " + sortName + sortUpOrDown0;
        } else {
            int endIndex = startIndex + pageSize;
            if (endIndex > maxCount) {
                pageSize = pageSize - (endIndex - maxCount);
            }
            sql = "SELECT * FROM " + DatabaseManager.STOCK_TABLE_NAME + " w1 " +
                    " WHERE " + sortName + " IN ( " +
                    "SELECT top " + pageSize + " " + sortName + " FROM " +
                    "( " +
                    "SELECT top " + endIndex + " " + sortName + " FROM " + DatabaseManager.STOCK_TABLE_NAME + " ORDER BY " + sortName + sortUpOrDown0 +
                    ") w ORDER BY w." + sortName + sortUpOrDown1 +
                    ") " +
                    " ORDER BY w1." + sortName + sortUpOrDown0;
            //sql="select * from ( select top 10 * from ( select top 10 * from stock order by stockid desc ) a order by stockid asc ) b order by stockid desc";
        }

        List<StockInfo> result = null;
        try {
            Statement stmt = con.createStatement();//创建Statement
            ResultSet rs = stmt.executeQuery(sql);//ResultSet类似Cursor
            result = collectStockInfoList(rs);
            stmt.close();
        } catch (Exception e) {
            e.printStackTrace();
        }

        if (con != null)
            con.close();
        return result;
    }

    public List<StockInfo> getBySpell(String stockSpell, int sortMode) throws Exception {
        // 加载驱动程序
        Class.forName("net.sourceforge.jtds.jdbc.Driver");
        Connection con = DriverManager.getConnection("jdbc:jtds:sqlserver://" + BarApplication.getInstance().mSqlServerHost + "/fastfood", BarApplication.getInstance().mSqlServerUser, BarApplication.getInstance().mSqlServerPassword);
        String sortName = DatabaseManager.STOCK_SPELL;
        String sortUpOrDown0 = " ASC ";
        String sortUpOrDown1 = " DESC ";
        switch (sortMode) {
            case 0:
                sortName = DatabaseManager.STOCK_SPELL;
                sortUpOrDown0 = " ASC ";
                sortUpOrDown1 = " DESC ";
                break;
            case 1:
                sortName = DatabaseManager.STOCK_SPELL;
                sortUpOrDown0 = " DESC ";
                sortUpOrDown1 = " ASC ";
                break;
            case 2:
                sortName = DatabaseManager.STOCK_STOCKID;
                sortUpOrDown0 = " ASC ";
                sortUpOrDown1 = " DESC ";
                break;
            case 3:
                sortName = DatabaseManager.STOCK_STOCKID;
                sortUpOrDown0 = " DESC ";
                sortUpOrDown1 = " ASC ";
                break;
            case 4:
                sortName = DatabaseManager.STOCK_RETAIL_PRICE;
                sortUpOrDown0 = " ASC ";
                sortUpOrDown1 = " DESC ";
                break;
            case 5:
                sortName = DatabaseManager.STOCK_RETAIL_PRICE;
                sortUpOrDown0 = " DESC ";
                sortUpOrDown1 = " ASC ";
                break;
        }
        String sql = "SELECT " + DatabaseManager.STOCK_STOCKID + "," +
                DatabaseManager.STOCK_STOCKNAME + "," +
                DatabaseManager.STOCK_UNIT + "," +
                DatabaseManager.STOCK_TYPEID + "," +
                DatabaseManager.STOCK_COST + "," +
                DatabaseManager.STOCK_RETAIL_PRICE + "," +
                DatabaseManager.STOCK_MEMBER_PRICE + "," +
                DatabaseManager.STOCK_CAN_DISCOUNT + "," +
                DatabaseManager.STOCK_BARCODE + "," +
                DatabaseManager.STOCK_SPELL +
                " FROM " + DatabaseManager.STOCK_TABLE_NAME + " WHERE " + DatabaseManager.STOCK_SPELL + "  like '%" + stockSpell + "%'" + " ORDER BY " + sortName + sortUpOrDown0;
        Statement stmt = con.createStatement();//创建Statement
        ResultSet rs = stmt.executeQuery(sql);//ResultSet类似Cursor
        List<StockInfo> result = collectStockInfoList(rs);
        stmt.close();
        if (con != null)
            con.close();
        return result;
    }

    public List<StockInfo> getById(String stockId, int sortMode) throws Exception {
        // 加载驱动程序
        Class.forName("net.sourceforge.jtds.jdbc.Driver");
        Connection con = DriverManager.getConnection("jdbc:jtds:sqlserver://" + BarApplication.getInstance().mSqlServerHost + "/fastfood", BarApplication.getInstance().mSqlServerUser, BarApplication.getInstance().mSqlServerPassword);
        String sortName = DatabaseManager.STOCK_SPELL;
        String sortUpOrDown0 = " ASC ";
        String sortUpOrDown1 = " DESC ";
        switch (sortMode) {
            case 0:
                sortName = DatabaseManager.STOCK_SPELL;
                sortUpOrDown0 = " ASC ";
                sortUpOrDown1 = " DESC ";
                break;
            case 1:
                sortName = DatabaseManager.STOCK_SPELL;
                sortUpOrDown0 = " DESC ";
                sortUpOrDown1 = " ASC ";
                break;
            case 2:
                sortName = DatabaseManager.STOCK_STOCKID;
                sortUpOrDown0 = " ASC ";
                sortUpOrDown1 = " DESC ";
                break;
            case 3:
                sortName = DatabaseManager.STOCK_STOCKID;
                sortUpOrDown0 = " DESC ";
                sortUpOrDown1 = " ASC ";
                break;
            case 4:
                sortName = DatabaseManager.STOCK_RETAIL_PRICE;
                sortUpOrDown0 = " ASC ";
                sortUpOrDown1 = " DESC ";
                break;
            case 5:
                sortName = DatabaseManager.STOCK_RETAIL_PRICE;
                sortUpOrDown0 = " DESC ";
                sortUpOrDown1 = " ASC ";
                break;
        }
        String sql = "SELECT " + DatabaseManager.STOCK_STOCKID + "," +
                DatabaseManager.STOCK_STOCKNAME + "," +
                DatabaseManager.STOCK_UNIT + "," +
                DatabaseManager.STOCK_TYPEID + "," +
                DatabaseManager.STOCK_COST + "," +
                DatabaseManager.STOCK_RETAIL_PRICE + "," +
                DatabaseManager.STOCK_MEMBER_PRICE + "," +
                DatabaseManager.STOCK_CAN_DISCOUNT + "," +
                DatabaseManager.STOCK_BARCODE + "," +
                DatabaseManager.STOCK_SPELL +
                " FROM " + DatabaseManager.STOCK_TABLE_NAME + " WHERE " + DatabaseManager.STOCK_STOCKID + "  like '%" + stockId + "%'" + " ORDER BY " + sortName + sortUpOrDown0;
        Statement stmt = con.createStatement();//创建Statement
        ResultSet rs = stmt.executeQuery(sql);//ResultSet类似Cursor
        List<StockInfo> result = collectStockInfoList(rs);
        stmt.close();
        if (con != null)
            con.close();
        return result;
    }

    public List<StockInfo> getById1(String stockId) throws Exception {
        // 加载驱动程序
        Class.forName("net.sourceforge.jtds.jdbc.Driver");
        Connection con = DriverManager.getConnection("jdbc:jtds:sqlserver://" + BarApplication.getInstance().mSqlServerHost + "/fastfood", BarApplication.getInstance().mSqlServerUser, BarApplication.getInstance().mSqlServerPassword);
        String sql = "SELECT " + DatabaseManager.STOCK_STOCKID + "," +
                DatabaseManager.STOCK_STOCKNAME + "," +
                DatabaseManager.STOCK_UNIT + "," +
                DatabaseManager.STOCK_TYPEID + "," +
                DatabaseManager.STOCK_COST + "," +
                DatabaseManager.STOCK_RETAIL_PRICE + "," +
                DatabaseManager.STOCK_MEMBER_PRICE + "," +
                DatabaseManager.STOCK_CAN_DISCOUNT + "," +
                DatabaseManager.STOCK_BARCODE + "," +
                DatabaseManager.STOCK_SPELL +
                " FROM " + DatabaseManager.STOCK_TABLE_NAME + " WHERE " + DatabaseManager.STOCK_STOCKID + "=" + stockId;//查询表名为“stock”的所有内容
        Statement stmt = con.createStatement();//创建Statement
        ResultSet rs = stmt.executeQuery(sql);//ResultSet类似Cursor
        List<StockInfo> result = collectStockInfoList(rs);
        stmt.close();
        if (con != null)
            con.close();
        return result;
    }

    public List<StockInfo> getByBarcode(String barcode, int sortMode) throws Exception {
        // 加载驱动程序
        Class.forName("net.sourceforge.jtds.jdbc.Driver");
        Connection con = DriverManager.getConnection("jdbc:jtds:sqlserver://" + BarApplication.getInstance().mSqlServerHost + "/fastfood", BarApplication.getInstance().mSqlServerUser, BarApplication.getInstance().mSqlServerPassword);
        String sortName = DatabaseManager.STOCK_SPELL;
        String sortUpOrDown0 = " ASC ";
        String sortUpOrDown1 = " DESC ";
        switch (sortMode) {
            case 0:
                sortName = DatabaseManager.STOCK_SPELL;
                sortUpOrDown0 = " ASC ";
                sortUpOrDown1 = " DESC ";
                break;
            case 1:
                sortName = DatabaseManager.STOCK_SPELL;
                sortUpOrDown0 = " DESC ";
                sortUpOrDown1 = " ASC ";
                break;
            case 2:
                sortName = DatabaseManager.STOCK_STOCKID;
                sortUpOrDown0 = " ASC ";
                sortUpOrDown1 = " DESC ";
                break;
            case 3:
                sortName = DatabaseManager.STOCK_STOCKID;
                sortUpOrDown0 = " DESC ";
                sortUpOrDown1 = " ASC ";
                break;
            case 4:
                sortName = DatabaseManager.STOCK_RETAIL_PRICE;
                sortUpOrDown0 = " ASC ";
                sortUpOrDown1 = " DESC ";
                break;
            case 5:
                sortName = DatabaseManager.STOCK_RETAIL_PRICE;
                sortUpOrDown0 = " DESC ";
                sortUpOrDown1 = " ASC ";
                break;
        }
        String sql = "SELECT " + DatabaseManager.STOCK_STOCKID + "," +
                DatabaseManager.STOCK_STOCKNAME + "," +
                DatabaseManager.STOCK_UNIT + "," +
                DatabaseManager.STOCK_TYPEID + "," +
                DatabaseManager.STOCK_COST + "," +
                DatabaseManager.STOCK_RETAIL_PRICE + "," +
                DatabaseManager.STOCK_MEMBER_PRICE + "," +
                DatabaseManager.STOCK_CAN_DISCOUNT + "," +
                DatabaseManager.STOCK_BARCODE + "," +
                DatabaseManager.STOCK_SPELL +
                " FROM " + DatabaseManager.STOCK_TABLE_NAME + " WHERE " + DatabaseManager.STOCK_BARCODE + "  like '%" + barcode + "%'" + " ORDER BY " + sortName + sortUpOrDown0;
        Statement stmt = con.createStatement();//创建Statement
        ResultSet rs = stmt.executeQuery(sql);//ResultSet类似Cursor
        List<StockInfo> result = collectStockInfoList(rs);
        stmt.close();
        if (con != null)
            con.close();
        return result;
    }

    public List<StockInfo> getByName(String stockName, int sortMode) throws Exception {
        // 加载驱动程序
        Class.forName("net.sourceforge.jtds.jdbc.Driver");
        Connection con = DriverManager.getConnection("jdbc:jtds:sqlserver://" + BarApplication.getInstance().mSqlServerHost + "/fastfood", BarApplication.getInstance().mSqlServerUser, BarApplication.getInstance().mSqlServerPassword);
        String sortName = DatabaseManager.STOCK_SPELL;
        String sortUpOrDown0 = " ASC ";
        String sortUpOrDown1 = " DESC ";
        switch (sortMode) {
            case 0:
                sortName = DatabaseManager.STOCK_SPELL;
                sortUpOrDown0 = " ASC ";
                sortUpOrDown1 = " DESC ";
                break;
            case 1:
                sortName = DatabaseManager.STOCK_SPELL;
                sortUpOrDown0 = " DESC ";
                sortUpOrDown1 = " ASC ";
                break;
            case 2:
                sortName = DatabaseManager.STOCK_STOCKID;
                sortUpOrDown0 = " ASC ";
                sortUpOrDown1 = " DESC ";
                break;
            case 3:
                sortName = DatabaseManager.STOCK_STOCKID;
                sortUpOrDown0 = " DESC ";
                sortUpOrDown1 = " ASC ";
                break;
            case 4:
                sortName = DatabaseManager.STOCK_RETAIL_PRICE;
                sortUpOrDown0 = " ASC ";
                sortUpOrDown1 = " DESC ";
                break;
            case 5:
                sortName = DatabaseManager.STOCK_RETAIL_PRICE;
                sortUpOrDown0 = " DESC ";
                sortUpOrDown1 = " ASC ";
                break;
        }
        String sql = "SELECT " + DatabaseManager.STOCK_STOCKID + "," +
                DatabaseManager.STOCK_STOCKNAME + "," +
                DatabaseManager.STOCK_UNIT + "," +
                DatabaseManager.STOCK_TYPEID + "," +
                DatabaseManager.STOCK_COST + "," +
                DatabaseManager.STOCK_RETAIL_PRICE + "," +
                DatabaseManager.STOCK_MEMBER_PRICE + "," +
                DatabaseManager.STOCK_CAN_DISCOUNT + "," +
                DatabaseManager.STOCK_BARCODE + "," +
                DatabaseManager.STOCK_SPELL +
                " FROM " + DatabaseManager.STOCK_TABLE_NAME + " WHERE " + DatabaseManager.STOCK_STOCKNAME + "  like '%" + stockName + "%'" + " ORDER BY " + sortName + sortUpOrDown0;
        Statement stmt = con.createStatement();//创建Statement
        ResultSet rs = stmt.executeQuery(sql);//ResultSet类似Cursor
        List<StockInfo> result = collectStockInfoList(rs);
        stmt.close();
        if (con != null)
            con.close();
        return result;
    }

    public List<StockInfo> getByTypeId(String stockTypeId, int startIndex, int pageSize, int maxCount, int sortMode) throws Exception {
        // 加载驱动程序
        Class.forName("net.sourceforge.jtds.jdbc.Driver");
        Connection con = DriverManager.getConnection("jdbc:jtds:sqlserver://" + BarApplication.getInstance().mSqlServerHost + "/fastfood", BarApplication.getInstance().mSqlServerUser, BarApplication.getInstance().mSqlServerPassword);
        String sortName = DatabaseManager.STOCK_STOCKID;
        String sortUpOrDown0 = " ASC ";
        String sortUpOrDown1 = " DESC ";
        switch (sortMode) {
            case 0:
                sortName = DatabaseManager.STOCK_SPELL;
                sortUpOrDown0 = " ASC ";
                sortUpOrDown1 = " DESC ";
                break;
            case 1:
                sortName = DatabaseManager.STOCK_SPELL;
                sortUpOrDown0 = " DESC ";
                sortUpOrDown1 = " ASC ";
                break;
            case 2:
                sortName = DatabaseManager.STOCK_STOCKID;
                sortUpOrDown0 = " ASC ";
                sortUpOrDown1 = " DESC ";
                break;
            case 3:
                sortName = DatabaseManager.STOCK_STOCKID;
                sortUpOrDown0 = " DESC ";
                sortUpOrDown1 = " ASC ";
                break;
            case 4:
                sortName = DatabaseManager.STOCK_RETAIL_PRICE;
                sortUpOrDown0 = " ASC ";
                sortUpOrDown1 = " DESC ";
                break;
            case 5:
                sortName = DatabaseManager.STOCK_RETAIL_PRICE;
                sortUpOrDown0 = " DESC ";
                sortUpOrDown1 = " ASC ";
                break;
        }
        String sql = "";
        if (pageSize == -1) {
            sql = "SELECT " + DatabaseManager.STOCK_STOCKID + "," +
                    DatabaseManager.STOCK_STOCKNAME + "," +
                    DatabaseManager.STOCK_UNIT + "," +
                    DatabaseManager.STOCK_TYPEID + "," +
                    DatabaseManager.STOCK_COST + "," +
                    DatabaseManager.STOCK_RETAIL_PRICE + "," +
                    DatabaseManager.STOCK_MEMBER_PRICE + "," +
                    DatabaseManager.STOCK_CAN_DISCOUNT + "," +
                    DatabaseManager.STOCK_BARCODE + "," +
                    DatabaseManager.STOCK_SPELL +
                    " FROM " + DatabaseManager.STOCK_TABLE_NAME + " WHERE " + DatabaseManager.STOCK_TYPEID + "=" + stockTypeId + " ORDER BY " + sortName + sortUpOrDown0;
        } else {
            int endIndex = startIndex + pageSize;
            if (endIndex > maxCount) {
                pageSize = pageSize - (endIndex - maxCount);
            }
            sql = "SELECT * FROM " + DatabaseManager.STOCK_TABLE_NAME + " w1 " +
                    " WHERE " + sortName + " IN ( " +
                    "SELECT top " + pageSize + " " + sortName + " FROM " +
                    "( " +
                    "SELECT top " + endIndex + " " + sortName + " FROM " + DatabaseManager.STOCK_TABLE_NAME +
                    " WHERE " + DatabaseManager.STOCK_TYPEID + "=" + stockTypeId + " ORDER BY " + sortName + sortUpOrDown0 +
                    ") w ORDER BY w." + sortName + sortUpOrDown1 +
                    ") " +
                    " ORDER BY w1." + sortName + sortUpOrDown0;
        }

        List<StockInfo> result = null;
        try {
            Statement stmt = con.createStatement();//创建Statement
            ResultSet rs = stmt.executeQuery(sql);//ResultSet类似Cursor
            result = collectStockInfoList(rs);
            stmt.close();
        } catch (Exception e) {
            e.printStackTrace();
        }

        if (con != null)
            con.close();
        return result;
    }

    public long insert(List<ContentValues> valuesList) throws Exception {
        long row = 0;
        // 加载驱动程序
        Class.forName("net.sourceforge.jtds.jdbc.Driver");
        Connection con = DriverManager.getConnection("jdbc:jtds:sqlserver://" + BarApplication.getInstance().mSqlServerHost + "/fastfood", BarApplication.getInstance().mSqlServerUser, BarApplication.getInstance().mSqlServerPassword);
        Statement stmt = con.createStatement();//创建Statement
        for (ContentValues values : valuesList) {
            String sql = "insert into " + DatabaseManager.STOCK_TABLE_NAME + "(" +
                    DatabaseManager.STOCK_STOCKID + "," +
                    DatabaseManager.STOCK_STOCKNAME + "," +
                    DatabaseManager.STOCK_GROUPID + "," +
                    DatabaseManager.STOCK_UNIT + "," +
                    DatabaseManager.STOCK_TYPEID + "," +
                    DatabaseManager.STOCK_COST + "," +
                    DatabaseManager.STOCK_RETAIL_PRICE + "," +
                    DatabaseManager.STOCK_MEMBER_PRICE + "," +
                    DatabaseManager.STOCK_WHOLE_PRICE + "," +
                    DatabaseManager.STOCK_CAN_DISCOUNT + "," +
                    DatabaseManager.STOCK_FRONTEDN + "," +
                    DatabaseManager.STOCK_PAUSE + "," +
                    DatabaseManager.STOCK_CLOSEOFF + "," +
                    DatabaseManager.STOCK_STORABLE + "," +
                    DatabaseManager.STOCK_STOCKTYPE + "," +
                    DatabaseManager.STOCK_BARCODE + "," +
                    DatabaseManager.STOCK_STOCKPOS + "," +
                    DatabaseManager.STOCK_SPELL + "," +
                    DatabaseManager.STOCK_SUIT_FLAGE + ") values" + "('" +
                    values.get(DatabaseManager.STOCK_STOCKID) + "','" +
                    values.get(DatabaseManager.STOCK_STOCKNAME) + "','" +
                    values.get(DatabaseManager.STOCK_GROUPID) + "','" +
                    values.get(DatabaseManager.STOCK_UNIT) + "','" +
                    values.get(DatabaseManager.STOCK_TYPEID) + "'," +
                    values.get(DatabaseManager.STOCK_COST) + "," +
                    values.get(DatabaseManager.STOCK_RETAIL_PRICE) + "," +
                    values.get(DatabaseManager.STOCK_MEMBER_PRICE) + "," +
                    values.get(DatabaseManager.STOCK_WHOLE_PRICE) + ",'" +
                    values.get(DatabaseManager.STOCK_CAN_DISCOUNT) + "','" +
                    values.get(DatabaseManager.STOCK_FRONTEDN) + "','" +
                    values.get(DatabaseManager.STOCK_PAUSE) + "','" +
                    values.get(DatabaseManager.STOCK_CLOSEOFF) + "','" +
                    values.get(DatabaseManager.STOCK_STORABLE) + "','" +
                    values.get(DatabaseManager.STOCK_STOCKTYPE) + "','" +
                    values.get(DatabaseManager.STOCK_BARCODE) + "','" +
                    values.get(DatabaseManager.STOCK_STOCKPOS) + "','" +
                    values.get(DatabaseManager.STOCK_SPELL) + "','" +
                    values.get(DatabaseManager.STOCK_SUIT_FLAGE) + "')";
            stmt.executeUpdate(sql);//ResultSet类似Cursor
            row = row + stmt.getUpdateCount();
            if (row < 1) {
                row = -1;
                break;
            }
        }

        stmt.close();
        if (con != null)
            con.close();
        return row;
    }

    public long replace(ContentValues values) throws Exception {
        long row = -1;
        String stockId = (String) values.get(DatabaseManager.STOCK_STOCKID);
        // 加载驱动程序
        Class.forName("net.sourceforge.jtds.jdbc.Driver");
        Connection con = DriverManager.getConnection("jdbc:jtds:sqlserver://" + BarApplication.getInstance().mSqlServerHost + "/fastfood", BarApplication.getInstance().mSqlServerUser, BarApplication.getInstance().mSqlServerPassword);
        String sql = "update " + DatabaseManager.STOCK_TABLE_NAME + " set " +
                DatabaseManager.STOCK_STOCKID + "='" + values.get(DatabaseManager.STOCK_STOCKID) + "'," +
                DatabaseManager.STOCK_STOCKNAME + "='" + values.get(DatabaseManager.STOCK_STOCKNAME) + "'," +
                DatabaseManager.STOCK_GROUPID + "='" + values.get(DatabaseManager.STOCK_GROUPID) + "'," +
                DatabaseManager.STOCK_UNIT + "='" + values.get(DatabaseManager.STOCK_UNIT) + "'," +
                DatabaseManager.STOCK_TYPEID + "='" + values.get(DatabaseManager.STOCK_TYPEID) + "'," +
                DatabaseManager.STOCK_COST + "=" + values.get(DatabaseManager.STOCK_COST) + "," +
                DatabaseManager.STOCK_RETAIL_PRICE + "=" + values.get(DatabaseManager.STOCK_RETAIL_PRICE) + "," +
                DatabaseManager.STOCK_MEMBER_PRICE + "=" + values.get(DatabaseManager.STOCK_MEMBER_PRICE) + "," +
                DatabaseManager.STOCK_WHOLE_PRICE + "=" + values.get(DatabaseManager.STOCK_WHOLE_PRICE) + "," +
                DatabaseManager.STOCK_CAN_DISCOUNT + "='" + values.get(DatabaseManager.STOCK_CAN_DISCOUNT) + "'," +
                DatabaseManager.STOCK_FRONTEDN + "='" + values.get(DatabaseManager.STOCK_FRONTEDN) + "'," +
                DatabaseManager.STOCK_PAUSE + "='" + values.get(DatabaseManager.STOCK_PAUSE) + "'," +
                DatabaseManager.STOCK_CLOSEOFF + "='" + values.get(DatabaseManager.STOCK_CLOSEOFF) + "'," +
                DatabaseManager.STOCK_STORABLE + "='" + values.get(DatabaseManager.STOCK_STORABLE) + "'," +
                DatabaseManager.STOCK_STOCKTYPE + "='" + values.get(DatabaseManager.STOCK_STOCKTYPE) + "'," +
                DatabaseManager.STOCK_BARCODE + "='" + values.get(DatabaseManager.STOCK_BARCODE) + "'," +
                DatabaseManager.STOCK_STOCKPOS + "='" + values.get(DatabaseManager.STOCK_STOCKPOS) + "'," +
                DatabaseManager.STOCK_SPELL + "='" + values.get(DatabaseManager.STOCK_SPELL) + "'," +
                DatabaseManager.STOCK_SUIT_FLAGE + "='" + values.get(DatabaseManager.STOCK_SUIT_FLAGE) + "'" +
                " WHERE " + DatabaseManager.STOCK_STOCKID + "=" + stockId;
        Statement stmt = con.createStatement();//创建Statement
        stmt.executeUpdate(sql);//ResultSet类似Cursor
        row = stmt.getUpdateCount();
        stmt.close();
        if (con != null)
            con.close();
        return row;
    }

    public long delete(String stockId) throws Exception {
        long row = -1;
        // 加载驱动程序
        Class.forName("net.sourceforge.jtds.jdbc.Driver");
        Connection con = DriverManager.getConnection("jdbc:jtds:sqlserver://" + BarApplication.getInstance().mSqlServerHost + "/fastfood", BarApplication.getInstance().mSqlServerUser, BarApplication.getInstance().mSqlServerPassword);
        String sql = "delete from " + DatabaseManager.STOCK_TABLE_NAME + " WHERE " + DatabaseManager.STOCK_STOCKID + "=" + stockId;
        Statement stmt = con.createStatement();//创建Statement
        stmt.executeUpdate(sql);//ResultSet类似Cursor
        row = stmt.getUpdateCount();
        stmt.close();
        if (con != null)
            con.close();
        return row;
    }

    public Map<String, String> getAllStockType() throws Exception {
        // 加载驱动程序
        Class.forName("net.sourceforge.jtds.jdbc.Driver");
        Connection con = DriverManager.getConnection("jdbc:jtds:sqlserver://" + BarApplication.getInstance().mSqlServerHost + "/fastfood", BarApplication.getInstance().mSqlServerUser, BarApplication.getInstance().mSqlServerPassword);
        String sql = "SELECT * FROM " + DatabaseManager.STOCK_TYPE_TABLE_NAME;//查询表名为“stock”的所有内容
        Statement stmt = con.createStatement();//创建Statement
        ResultSet rs = stmt.executeQuery(sql);//ResultSet类似Cursor
        Map<String, String> stockTypeMap = new TreeMap<String, String>();
        stockTypeMap.put("-1", "全部");
        if (rs != null) {
            while (rs.next()) {
                stockTypeMap.put(rs.getString(DatabaseManager.STOCK_TYPE_TYPEID), rs.getString(DatabaseManager.STOCK_TYPE_TYPENAME));
            }
            rs.close();
        }
        stmt.close();
        if (con != null)
            con.close();
        return stockTypeMap;
    }

    public int getMemberCount(String memberPointStr) throws Exception {
        // 加载驱动程序
        Class.forName("net.sourceforge.jtds.jdbc.Driver");
        Connection con = DriverManager.getConnection("jdbc:jtds:sqlserver://" + BarApplication.getInstance().mSqlServerHost + "/fastfood", BarApplication.getInstance().mSqlServerUser, BarApplication.getInstance().mSqlServerPassword);
        String sql = "";
        if (!memberPointStr.equals("-1"))
            sql = "select count(1) as totalCount from " + DatabaseManager.MEMBER_TABLE_NAME + " WHERE " + DatabaseManager.MEMBER_POINT + " >= " + memberPointStr;
        else
            sql = "select count(1) as totalCount from " + DatabaseManager.MEMBER_TABLE_NAME;
        int totalCount = -1;
        try {
            Statement stmt = con.createStatement();//创建Statement
            ResultSet rs = stmt.executeQuery(sql);//ResultSet类似Cursor
            if (rs.next()) {
                totalCount = rs.getInt(1);
            }
            stmt.close();
        } catch (Exception e) {
            e.printStackTrace();
        }

        if (con != null)
            con.close();
        return totalCount;
    }

    private List<Member> collectMemberInfoList(ResultSet rs) throws Exception {
        List<Member> result = new ArrayList<Member>();
        if (rs != null) {
            while (rs.next()) {
                Member memberInfo = new Member();
                memberInfo.memberId = rs.getString(DatabaseManager.MEMBER_MEMBERID).trim();
                memberInfo.memberName = rs.getString(DatabaseManager.MEMBER_NAME).trim();
                memberInfo.memberSex = rs.getString(DatabaseManager.MEMBER_SEX).trim();
                memberInfo.memberMobile = rs.getString(DatabaseManager.MEMBER_MOBILE).trim();
                memberInfo.memberType = rs.getString(DatabaseManager.MEMBER_MEMBERTYPE).trim();
                memberInfo.memberCardid = rs.getString(DatabaseManager.MEMBER_CARDID).trim();
                memberInfo.memberModifySn = rs.getInt(DatabaseManager.MEMBER_MODIFY_SN);
                memberInfo.memberPoint = rs.getFloat(DatabaseManager.MEMBER_POINT);
                memberInfo.memberTotalConsume = rs.getFloat(DatabaseManager.MEMBER_TOTAL_CONSUME);
                memberInfo.memberBirthday = rs.getDate(DatabaseManager.MEMBER_BIRTHDAY);
                memberInfo.memberCreateDate = rs.getDate(DatabaseManager.MEMBER_CREATE_DATE);
                String str = "#";
                if (memberInfo.memberName.length() != 0)
                    str = Utils.cn2FirstSpell(memberInfo.memberName).substring(0, 1);
                if (str.matches("[A-Z]")) {
                    memberInfo.setSortLetters(str);
                } else {
                    memberInfo.setSortLetters("#");
                }

                result.add(memberInfo);
            }
            rs.close();
        }
        return result;
    }

    public List<Member> getAllMember(int startIndex, int pageSize, int maxCount, String memberPointStr, int sortMode) throws Exception {
        // 加载驱动程序
        Class.forName("net.sourceforge.jtds.jdbc.Driver");
        Connection con = DriverManager.getConnection("jdbc:jtds:sqlserver://" + BarApplication.getInstance().mSqlServerHost + "/fastfood", BarApplication.getInstance().mSqlServerUser, BarApplication.getInstance().mSqlServerPassword);
        String sortName = DatabaseManager.MEMBER_NAME;
        String sortUpOrDown0 = " ASC ";
        String sortUpOrDown1 = " DESC ";
        switch (sortMode) {
            case 0:
                sortName = DatabaseManager.MEMBER_NAME;
                sortUpOrDown0 = " ASC ";
                sortUpOrDown1 = " DESC ";
                break;
            case 1:
                sortName = DatabaseManager.MEMBER_NAME;
                sortUpOrDown0 = " DESC ";
                sortUpOrDown1 = " ASC ";
                break;
            case 2:
                sortName = DatabaseManager.MEMBER_POINT;
                sortUpOrDown0 = " ASC ";
                sortUpOrDown1 = " DESC ";
                break;
            case 3:
                sortName = DatabaseManager.MEMBER_POINT;
                sortUpOrDown0 = " DESC ";
                sortUpOrDown1 = " ASC ";
                break;
            case 4:
                sortName = DatabaseManager.MEMBER_TOTAL_CONSUME;
                sortUpOrDown0 = " ASC ";
                sortUpOrDown1 = " DESC ";
                break;
            case 5:
                sortName = DatabaseManager.MEMBER_TOTAL_CONSUME;
                sortUpOrDown0 = " DESC ";
                sortUpOrDown1 = " ASC ";
                break;
        }

        String sql = "";
        if (pageSize == -1) {
            sql = "SELECT " + DatabaseManager.MEMBER_MEMBERID + "," +
                    DatabaseManager.MEMBER_NAME + "," +
                    DatabaseManager.MEMBER_SEX + "," +
                    DatabaseManager.MEMBER_BIRTHDAY + "," +
                    DatabaseManager.MEMBER_CREATE_DATE + "," +
                    DatabaseManager.MEMBER_MOBILE + "," +
                    DatabaseManager.MEMBER_POINT + "," +
                    DatabaseManager.MEMBER_MEMBERTYPE + "," +
                    DatabaseManager.MEMBER_CARDID + "," +
                    DatabaseManager.MEMBER_MODIFY_SN + "," +
                    DatabaseManager.MEMBER_TOTAL_CONSUME +
                    " FROM " + DatabaseManager.MEMBER_TABLE_NAME + " ORDER BY " + sortName + sortUpOrDown0;
        } else {
            int endIndex = startIndex + pageSize;
            if (endIndex > maxCount) {
                pageSize = pageSize - (endIndex - maxCount);
            }
//            sql =  "SELECT top " + pageSize + " * FROM " +
//                    "( " +
//                    "SELECT top " + endIndex + " * FROM " + DatabaseManager.MEMBER_TABLE_NAME +
//                    " WHERE " + DatabaseManager.MEMBER_POINT + " >= " + memberPointStr + " ORDER BY " + sortName + sortUpOrDown0 +
//                    ") w ORDER BY w." + sortName + sortUpOrDown1 ;

            sql = "SELECT * FROM " + DatabaseManager.MEMBER_TABLE_NAME + " w1 " +
                    " WHERE " + sortName + " IN ( " +
                    "SELECT top " + pageSize + " " + sortName + " FROM " +
                    "( " +
                    "SELECT top " + endIndex + " " + sortName + " FROM " + DatabaseManager.MEMBER_TABLE_NAME +
                    " WHERE " + DatabaseManager.MEMBER_POINT + " >= " + memberPointStr + " ORDER BY " + sortName + sortUpOrDown0 +
                    ") w ORDER BY w." + sortName + sortUpOrDown1 +
                    ") " +
                    " ORDER BY w1." + sortName + sortUpOrDown0;
        }

        List<Member> result = null;
        try {
            Statement stmt = con.createStatement();//创建Statement
            ResultSet rs = stmt.executeQuery(sql);//ResultSet类似Cursor
            result = collectMemberInfoList(rs);
            stmt.close();
        } catch (Exception e) {
            e.printStackTrace();
        }

        if (con != null)
            con.close();
        return result;
    }

    public List<Member> getByMemberId(String cardid, int sortMode) throws Exception {
        // 加载驱动程序
        Class.forName("net.sourceforge.jtds.jdbc.Driver");
        Connection con = DriverManager.getConnection("jdbc:jtds:sqlserver://" + BarApplication.getInstance().mSqlServerHost + "/fastfood", BarApplication.getInstance().mSqlServerUser, BarApplication.getInstance().mSqlServerPassword);
        String sortName = DatabaseManager.MEMBER_NAME;
        String sortUpOrDown0 = " ASC ";
        String sortUpOrDown1 = " DESC ";
        switch (sortMode) {
            case 0:
                sortName = DatabaseManager.MEMBER_NAME;
                sortUpOrDown0 = " ASC ";
                sortUpOrDown1 = " DESC ";
                break;
            case 1:
                sortName = DatabaseManager.MEMBER_NAME;
                sortUpOrDown0 = " DESC ";
                sortUpOrDown1 = " ASC ";
                break;
            case 2:
                sortName = DatabaseManager.MEMBER_POINT;
                sortUpOrDown0 = " ASC ";
                sortUpOrDown1 = " DESC ";
                break;
            case 3:
                sortName = DatabaseManager.MEMBER_POINT;
                sortUpOrDown0 = " DESC ";
                sortUpOrDown1 = " ASC ";
                break;
            case 4:
                sortName = DatabaseManager.MEMBER_TOTAL_CONSUME;
                sortUpOrDown0 = " ASC ";
                sortUpOrDown1 = " DESC ";
                break;
            case 5:
                sortName = DatabaseManager.MEMBER_TOTAL_CONSUME;
                sortUpOrDown0 = " DESC ";
                sortUpOrDown1 = " ASC ";
                break;
        }
        String sql = "SELECT " + DatabaseManager.MEMBER_MEMBERID + "," +
                DatabaseManager.MEMBER_NAME + "," +
                DatabaseManager.MEMBER_SEX + "," +
                DatabaseManager.MEMBER_BIRTHDAY + "," +
                DatabaseManager.MEMBER_CREATE_DATE + "," +
                DatabaseManager.MEMBER_MOBILE + "," +
                DatabaseManager.MEMBER_POINT + "," +
                DatabaseManager.MEMBER_MEMBERTYPE + "," +
                DatabaseManager.MEMBER_CARDID + "," +
                DatabaseManager.MEMBER_MODIFY_SN + "," +
                DatabaseManager.MEMBER_TOTAL_CONSUME +
                " FROM " + DatabaseManager.MEMBER_TABLE_NAME + " WHERE " + DatabaseManager.MEMBER_CARDID + "  like '%" + cardid + "%'" + " ORDER BY " + sortName + sortUpOrDown0;
        Statement stmt = con.createStatement();//创建Statement
        ResultSet rs = stmt.executeQuery(sql);//ResultSet类似Cursor
        List<Member> result = collectMemberInfoList(rs);
        stmt.close();
        if (con != null)
            con.close();
        return result;
    }

    public List<Member> getByMemberName(String name, int sortMode) throws Exception {
        // 加载驱动程序
        Class.forName("net.sourceforge.jtds.jdbc.Driver");
        Connection con = DriverManager.getConnection("jdbc:jtds:sqlserver://" + BarApplication.getInstance().mSqlServerHost + "/fastfood", BarApplication.getInstance().mSqlServerUser, BarApplication.getInstance().mSqlServerPassword);
        String sortName = DatabaseManager.MEMBER_NAME;
        String sortUpOrDown0 = " ASC ";
        String sortUpOrDown1 = " DESC ";
        switch (sortMode) {
            case 0:
                sortName = DatabaseManager.MEMBER_NAME;
                sortUpOrDown0 = " ASC ";
                sortUpOrDown1 = " DESC ";
                break;
            case 1:
                sortName = DatabaseManager.MEMBER_NAME;
                sortUpOrDown0 = " DESC ";
                sortUpOrDown1 = " ASC ";
                break;
            case 2:
                sortName = DatabaseManager.MEMBER_POINT;
                sortUpOrDown0 = " ASC ";
                sortUpOrDown1 = " DESC ";
                break;
            case 3:
                sortName = DatabaseManager.MEMBER_POINT;
                sortUpOrDown0 = " DESC ";
                sortUpOrDown1 = " ASC ";
                break;
            case 4:
                sortName = DatabaseManager.MEMBER_TOTAL_CONSUME;
                sortUpOrDown0 = " ASC ";
                sortUpOrDown1 = " DESC ";
                break;
            case 5:
                sortName = DatabaseManager.MEMBER_TOTAL_CONSUME;
                sortUpOrDown0 = " DESC ";
                sortUpOrDown1 = " ASC ";
                break;
        }
        String sql = "SELECT " + DatabaseManager.MEMBER_MEMBERID + "," +
                DatabaseManager.MEMBER_NAME + "," +
                DatabaseManager.MEMBER_SEX + "," +
                DatabaseManager.MEMBER_BIRTHDAY + "," +
                DatabaseManager.MEMBER_CREATE_DATE + "," +
                DatabaseManager.MEMBER_MOBILE + "," +
                DatabaseManager.MEMBER_POINT + "," +
                DatabaseManager.MEMBER_MEMBERTYPE + "," +
                DatabaseManager.MEMBER_CARDID + "," +
                DatabaseManager.MEMBER_MODIFY_SN + "," +
                DatabaseManager.MEMBER_TOTAL_CONSUME +
                " FROM " + DatabaseManager.MEMBER_TABLE_NAME + " WHERE " + DatabaseManager.MEMBER_NAME + "  like '%" + name + "%'" + " ORDER BY " + sortName + sortUpOrDown0;
        Statement stmt = con.createStatement();//创建Statement
        ResultSet rs = stmt.executeQuery(sql);//ResultSet类似Cursor
        List<Member> result = collectMemberInfoList(rs);
        stmt.close();
        if (con != null)
            con.close();
        return result;
    }

    public List<Member> getByMemberMobile(String mobile, int sortMode) throws Exception {
        // 加载驱动程序
        Class.forName("net.sourceforge.jtds.jdbc.Driver");
        Connection con = DriverManager.getConnection("jdbc:jtds:sqlserver://" + BarApplication.getInstance().mSqlServerHost + "/fastfood", BarApplication.getInstance().mSqlServerUser, BarApplication.getInstance().mSqlServerPassword);
        String sortName = DatabaseManager.MEMBER_NAME;
        String sortUpOrDown0 = " ASC ";
        String sortUpOrDown1 = " DESC ";
        switch (sortMode) {
            case 0:
                sortName = DatabaseManager.MEMBER_NAME;
                sortUpOrDown0 = " ASC ";
                sortUpOrDown1 = " DESC ";
                break;
            case 1:
                sortName = DatabaseManager.MEMBER_NAME;
                sortUpOrDown0 = " DESC ";
                sortUpOrDown1 = " ASC ";
                break;
            case 2:
                sortName = DatabaseManager.MEMBER_POINT;
                sortUpOrDown0 = " ASC ";
                sortUpOrDown1 = " DESC ";
                break;
            case 3:
                sortName = DatabaseManager.MEMBER_POINT;
                sortUpOrDown0 = " DESC ";
                sortUpOrDown1 = " ASC ";
                break;
            case 4:
                sortName = DatabaseManager.MEMBER_TOTAL_CONSUME;
                sortUpOrDown0 = " ASC ";
                sortUpOrDown1 = " DESC ";
                break;
            case 5:
                sortName = DatabaseManager.MEMBER_TOTAL_CONSUME;
                sortUpOrDown0 = " DESC ";
                sortUpOrDown1 = " ASC ";
                break;
        }
        String sql = "SELECT " + DatabaseManager.MEMBER_MEMBERID + "," +
                DatabaseManager.MEMBER_NAME + "," +
                DatabaseManager.MEMBER_SEX + "," +
                DatabaseManager.MEMBER_BIRTHDAY + "," +
                DatabaseManager.MEMBER_CREATE_DATE + "," +
                DatabaseManager.MEMBER_MOBILE + "," +
                DatabaseManager.MEMBER_POINT + "," +
                DatabaseManager.MEMBER_MEMBERTYPE + "," +
                DatabaseManager.MEMBER_CARDID + "," +
                DatabaseManager.MEMBER_MODIFY_SN + "," +
                DatabaseManager.MEMBER_TOTAL_CONSUME +
                " FROM " + DatabaseManager.MEMBER_TABLE_NAME + " WHERE " + DatabaseManager.MEMBER_MOBILE + "  like '%" + mobile + "%'" + " ORDER BY " + sortName + sortUpOrDown0;
        Statement stmt = con.createStatement();//创建Statement
        ResultSet rs = stmt.executeQuery(sql);//ResultSet类似Cursor
        List<Member> result = collectMemberInfoList(rs);
        stmt.close();
        if (con != null)
            con.close();
        return result;
    }

    public long deleteMember(String memberId) throws Exception {
        long row = -1;
        // 加载驱动程序
        Class.forName("net.sourceforge.jtds.jdbc.Driver");
        Connection con = DriverManager.getConnection("jdbc:jtds:sqlserver://" + BarApplication.getInstance().mSqlServerHost + "/fastfood", BarApplication.getInstance().mSqlServerUser, BarApplication.getInstance().mSqlServerPassword);
        String sql = "delete from " + DatabaseManager.MEMBER_TABLE_NAME + " WHERE " + DatabaseManager.MEMBER_MEMBERID + "=" + memberId;
        Statement stmt = con.createStatement();//创建Statement
        stmt.executeUpdate(sql);//ResultSet类似Cursor
        row = stmt.getUpdateCount();
        stmt.close();
        if (con != null)
            con.close();
        return row;
    }

    public long insertMember(ContentValues values) throws Exception {
        long row = -1;
        // 加载驱动程序
        Class.forName("net.sourceforge.jtds.jdbc.Driver");
        Connection con = DriverManager.getConnection("jdbc:jtds:sqlserver://" + BarApplication.getInstance().mSqlServerHost + "/fastfood", BarApplication.getInstance().mSqlServerUser, BarApplication.getInstance().mSqlServerPassword);
        String sql = "insert into " + DatabaseManager.MEMBER_TABLE_NAME + "(" +
                DatabaseManager.MEMBER_MEMBERID + "," +
                DatabaseManager.MEMBER_NAME + "," +
                DatabaseManager.MEMBER_SEX + "," +
                DatabaseManager.MEMBER_TELEPHONE + "," +
                DatabaseManager.MEMBER_MOBILE + "," +
                DatabaseManager.MEMBER_POST + "," +
                DatabaseManager.MEMBER_ADDR + "," +
                DatabaseManager.MEMBER_EMAIL + "," +
                DatabaseManager.MEMBER_MEMO + "," +
                DatabaseManager.MEMBER_BIRTHDAY + "," +
                DatabaseManager.MEMBER_CREATE_DATE + "," +
                DatabaseManager.MEMBER_CREATE_OPERID + "," +
                DatabaseManager.MEMBER_STATE + "," +
                DatabaseManager.MEMBER_AMOUNT + "," +
                DatabaseManager.MEMBER_OVERDRAFT + "," +
                DatabaseManager.MEMBER_PASSWORD + "," +
                DatabaseManager.MEMBER_DISCOUNTE + "," +
                DatabaseManager.MEMBER_POINT + "," +
                DatabaseManager.MEMBER_POINT_FLAG + "," +
                DatabaseManager.MEMBER_PRICE_KIND + "," +
                DatabaseManager.MEMBER_MEMBERTYPE + "," +
                DatabaseManager.MEMBER_CARDID + "," +
                DatabaseManager.MEMBER_CARD_MEDIUM + "," +
                DatabaseManager.MEMBER_MODIFY_SN + "," +
                DatabaseManager.MEMBER_CARD_RECHARGE + "," +
                DatabaseManager.MEMBER_CARD_PRESENT + "," +
                DatabaseManager.MEMBER_CARD_CONSUME + "," +
                DatabaseManager.MEMBER_TOTAL_CONSUME + "," +
                DatabaseManager.MEMBER_CREATE_SHOPID + "," +
                DatabaseManager.MEMBER_EXPIRE_DATE + ") values" + "('" +
                values.get(DatabaseManager.MEMBER_MEMBERID) + "','" +
                values.get(DatabaseManager.MEMBER_NAME) + "','" +
                values.get(DatabaseManager.MEMBER_SEX) + "','" +
                values.get(DatabaseManager.MEMBER_TELEPHONE) + "','" +
                values.get(DatabaseManager.MEMBER_MOBILE) + "','" +
                values.get(DatabaseManager.MEMBER_POST) + "','" +
                values.get(DatabaseManager.MEMBER_ADDR) + "','" +
                values.get(DatabaseManager.MEMBER_EMAIL) + "','" +
                values.get(DatabaseManager.MEMBER_MEMO) + "','" +
                values.get(DatabaseManager.MEMBER_BIRTHDAY) + "','" +
                values.get(DatabaseManager.MEMBER_CREATE_DATE) + "','" +
                values.get(DatabaseManager.MEMBER_CREATE_OPERID) + "','" +
                values.get(DatabaseManager.MEMBER_STATE) + "'," +
                values.get(DatabaseManager.MEMBER_AMOUNT) + ",'" +
                values.get(DatabaseManager.MEMBER_OVERDRAFT) + "','" +
                values.get(DatabaseManager.MEMBER_PASSWORD) + "'," +
                values.get(DatabaseManager.MEMBER_DISCOUNTE) + "," +
                values.get(DatabaseManager.MEMBER_POINT) + ",'" +
                values.get(DatabaseManager.MEMBER_POINT_FLAG) + "','" +
                values.get(DatabaseManager.MEMBER_PRICE_KIND) + "','" +
                values.get(DatabaseManager.MEMBER_MEMBERTYPE) + "','" +
                values.get(DatabaseManager.MEMBER_CARDID) + "'," +
                values.get(DatabaseManager.MEMBER_CARD_MEDIUM) + "," +
                values.get(DatabaseManager.MEMBER_MODIFY_SN) + "," +
                values.get(DatabaseManager.MEMBER_CARD_RECHARGE) + "," +
                values.get(DatabaseManager.MEMBER_CARD_PRESENT) + "," +
                values.get(DatabaseManager.MEMBER_CARD_CONSUME) + "," +
                values.get(DatabaseManager.MEMBER_TOTAL_CONSUME) + ",'" +
                values.get(DatabaseManager.MEMBER_CREATE_SHOPID) + "','" +
                values.get(DatabaseManager.MEMBER_EXPIRE_DATE) + "')";
        Statement stmt = con.createStatement();//创建Statement
        stmt.executeUpdate(sql);//ResultSet类似Cursor
        row = stmt.getUpdateCount();
        stmt.close();
        if (con != null)
            con.close();
        return row;
    }

    public long replaceMember(ContentValues values) throws Exception {
        long row = -1;
        String memberId = (String) values.get(DatabaseManager.MEMBER_MEMBERID);
        // 加载驱动程序
        Class.forName("net.sourceforge.jtds.jdbc.Driver");
        Connection con = DriverManager.getConnection("jdbc:jtds:sqlserver://" + BarApplication.getInstance().mSqlServerHost + "/fastfood", BarApplication.getInstance().mSqlServerUser, BarApplication.getInstance().mSqlServerPassword);
        String sql = "update " + DatabaseManager.MEMBER_TABLE_NAME + " set " +
                DatabaseManager.MEMBER_MEMBERID + "='" + values.get(DatabaseManager.MEMBER_MEMBERID) + "'," +
                DatabaseManager.MEMBER_NAME + "='" + values.get(DatabaseManager.MEMBER_NAME) + "'," +
                DatabaseManager.MEMBER_SEX + "='" + values.get(DatabaseManager.MEMBER_SEX) + "'," +
                DatabaseManager.MEMBER_TELEPHONE + "='" + values.get(DatabaseManager.MEMBER_TELEPHONE) + "'," +
                DatabaseManager.MEMBER_MOBILE + "='" + values.get(DatabaseManager.MEMBER_MOBILE) + "'," +
                DatabaseManager.MEMBER_POST + "='" + values.get(DatabaseManager.MEMBER_POST) + "'," +
                DatabaseManager.MEMBER_ADDR + "='" + values.get(DatabaseManager.MEMBER_ADDR) + "'," +
                DatabaseManager.MEMBER_EMAIL + "='" + values.get(DatabaseManager.MEMBER_EMAIL) + "'," +
                DatabaseManager.MEMBER_MEMO + "='" + values.get(DatabaseManager.MEMBER_MEMO) + "'," +
                DatabaseManager.MEMBER_BIRTHDAY + "='" + values.get(DatabaseManager.MEMBER_BIRTHDAY) + "'," +
                DatabaseManager.MEMBER_CREATE_DATE + "='" + values.get(DatabaseManager.MEMBER_CREATE_DATE) + "'," +
                DatabaseManager.MEMBER_CREATE_OPERID + "='" + values.get(DatabaseManager.MEMBER_CREATE_OPERID) + "'," +
                DatabaseManager.MEMBER_STATE + "='" + values.get(DatabaseManager.MEMBER_STATE) + "'," +
                DatabaseManager.MEMBER_AMOUNT + "=" + values.get(DatabaseManager.MEMBER_AMOUNT) + "," +
                DatabaseManager.MEMBER_OVERDRAFT + "='" + values.get(DatabaseManager.MEMBER_OVERDRAFT) + "'," +
                DatabaseManager.MEMBER_PASSWORD + "='" + values.get(DatabaseManager.MEMBER_PASSWORD) + "'," +
                DatabaseManager.MEMBER_DISCOUNTE + "=" + values.get(DatabaseManager.MEMBER_DISCOUNTE) + "," +
                DatabaseManager.MEMBER_POINT_FLAG + "='" + values.get(DatabaseManager.MEMBER_POINT_FLAG) + "'," +
                DatabaseManager.MEMBER_PRICE_KIND + "='" + values.get(DatabaseManager.MEMBER_PRICE_KIND) + "'," +
                DatabaseManager.MEMBER_MEMBERTYPE + "='" + values.get(DatabaseManager.MEMBER_MEMBERTYPE) + "'," +
                DatabaseManager.MEMBER_CARDID + "='" + values.get(DatabaseManager.MEMBER_CARDID) + "'," +
                DatabaseManager.MEMBER_CARD_MEDIUM + "=" + values.get(DatabaseManager.MEMBER_CARD_MEDIUM) + "," +
                DatabaseManager.MEMBER_MODIFY_SN + "=" + values.get(DatabaseManager.MEMBER_MODIFY_SN) + "," +
                DatabaseManager.MEMBER_CARD_RECHARGE + "=" + values.get(DatabaseManager.MEMBER_CARD_RECHARGE) + "," +
                DatabaseManager.MEMBER_CARD_PRESENT + "=" + values.get(DatabaseManager.MEMBER_CARD_PRESENT) + "," +
                DatabaseManager.MEMBER_CARD_CONSUME + "=" + values.get(DatabaseManager.MEMBER_CARD_CONSUME) + "," +
                DatabaseManager.MEMBER_CREATE_SHOPID + "='" + values.get(DatabaseManager.MEMBER_CREATE_SHOPID) + "'," +
                DatabaseManager.MEMBER_EXPIRE_DATE + "='" + values.get(DatabaseManager.MEMBER_EXPIRE_DATE) + "'" +
                " WHERE " + DatabaseManager.MEMBER_MEMBERID + "=" + memberId;
        Statement stmt = con.createStatement();//创建Statement
        stmt.executeUpdate(sql);//ResultSet类似Cursor
        row = stmt.getUpdateCount();
        stmt.close();
        if (con != null)
            con.close();
        return row;
    }

    private List<PayMaster> collectPayMasterList(ResultSet rs) throws Exception {
        List<PayMaster> result = new ArrayList<PayMaster>();
        if (rs != null) {
            while (rs.next()) {
                PayMaster payMasterInfo = new PayMaster();
                payMasterInfo.payMasterBillno = rs.getString(DatabaseManager.PAY_MASTER_BILLNO);
                payMasterInfo.payMasterOperdate = rs.getDate(DatabaseManager.PAY_MASTER_OPERDATE);
                payMasterInfo.payMasterOpertime = rs.getTime(DatabaseManager.PAY_MASTER_OPERDATE);
                payMasterInfo.payMasterBlotout = rs.getFloat(DatabaseManager.PAY_MASTER_BLOTOUT);
                payMasterInfo.payMasterTotal = rs.getFloat(DatabaseManager.PAY_MASTER_TOTAL);
                payMasterInfo.payMasterReceivable = rs.getFloat(DatabaseManager.PAY_MASTER_RECEIVABLE);
                result.add(payMasterInfo);
            }
            rs.close();
        }
        return result;
    }

    public List<PayMaster> getByPayMasterOperdate(String date) throws Exception {
        // 加载驱动程序
        Class.forName("net.sourceforge.jtds.jdbc.Driver");
        Connection con = DriverManager.getConnection("jdbc:jtds:sqlserver://" + BarApplication.getInstance().mSqlServerHost + "/fastfood", BarApplication.getInstance().mSqlServerUser, BarApplication.getInstance().mSqlServerPassword);
        String sql = "SELECT " + DatabaseManager.PAY_MASTER_BILLNO + "," +
                DatabaseManager.PAY_MASTER_OPERDATE + "," +
                DatabaseManager.PAY_MASTER_BLOTOUT + "," +
                DatabaseManager.PAY_MASTER_TOTAL + "," +
                DatabaseManager.PAY_MASTER_RECEIVABLE +
                " FROM " + DatabaseManager.PAY_MASTER_TABLE_NAME + " WHERE " + DatabaseManager.PAY_MASTER_OPERDATE + "  > '" + date + " 00:00:00' and " + DatabaseManager.PAY_MASTER_OPERDATE + "<'" + date + " 23:59:59'";
        Statement stmt = con.createStatement();//创建Statement
        ResultSet rs = stmt.executeQuery(sql);//ResultSet类似Cursor
        List<PayMaster> result = collectPayMasterList(rs);
        stmt.close();
        if (con != null)
            con.close();
        return result;
    }

    public long deletePayMaster(String deleteDate) throws Exception {
        long row = -1;
        // 加载驱动程序
        Class.forName("net.sourceforge.jtds.jdbc.Driver");
        Connection con = DriverManager.getConnection("jdbc:jtds:sqlserver://" + BarApplication.getInstance().mSqlServerHost + "/fastfood", BarApplication.getInstance().mSqlServerUser, BarApplication.getInstance().mSqlServerPassword);
        String sql = "delete from " + DatabaseManager.PAY_MASTER_TABLE_NAME + " WHERE " + DatabaseManager.PAY_MASTER_OPERDATE + "  > '" + deleteDate + " 00:00:00' and " + DatabaseManager.PAY_MASTER_OPERDATE + "<'" + deleteDate + " 23:59:59'";
        Statement stmt = con.createStatement();//创建Statement
        stmt.executeUpdate(sql);//ResultSet类似Cursor
        row = stmt.getUpdateCount();
        stmt.close();
        if (con != null)
            con.close();
        return row;
    }

    private List<SaleDetailInfo> collectSaleDetailList(ResultSet rs) throws Exception {
        List<SaleDetailInfo> result = new ArrayList<SaleDetailInfo>();
        HashMap<String, Object> item = new HashMap<String, Object>();
        HashMap<String, Object> item1 = new HashMap<String, Object>();
        if (rs != null) {
            while (rs.next()) {
                SaleDetailInfo saleDetailInfo = new SaleDetailInfo();
                saleDetailInfo.saleDetailBillno = rs.getString(DatabaseManager.SALE_DETAIL_BILLNO);
                saleDetailInfo.saleDetailStockid = rs.getString(DatabaseManager.SALE_DETAIL_STOCKID).trim();
                saleDetailInfo.saleDetailOperdate = rs.getDate(DatabaseManager.SALE_DETAIL_OPERATEDATE);
                saleDetailInfo.saleDetailOpertime = rs.getTime(DatabaseManager.SALE_DETAIL_OPERATEDATE);
                saleDetailInfo.saleDetailQuantity = rs.getFloat(DatabaseManager.SALE_DETAIL_QUANTITY);
                saleDetailInfo.saleDetailPrice = rs.getFloat(DatabaseManager.SALE_DETAIL_PRICE_ORIG);
                saleDetailInfo.saleDetailCost = rs.getFloat(DatabaseManager.SALE_DETAIL_COST);

                // 跳过重复的SaleDetail数据
                if ((String) item.get(saleDetailInfo.saleDetailOperdate.toString()) != null
                        && (String) item1.get(saleDetailInfo.saleDetailOpertime.toString()) != null) {
                    if (((String) item.get(saleDetailInfo.saleDetailOperdate.toString())).equals(saleDetailInfo.saleDetailOperdate.toString())
                            && ((String) item1.get(saleDetailInfo.saleDetailOpertime.toString())).equals(saleDetailInfo.saleDetailOpertime.toString())) {
                        continue;
                    }
                }

                item.put(saleDetailInfo.saleDetailOperdate.toString(), saleDetailInfo.saleDetailOperdate.toString());
                item1.put(saleDetailInfo.saleDetailOpertime.toString(), saleDetailInfo.saleDetailOpertime.toString());

                result.add(saleDetailInfo);
            }
            rs.close();
        }
        return result;
    }

    public List<SaleDetailInfo> getSaleDetailByBillno(String billno) throws Exception {
        // 加载驱动程序
        Class.forName("net.sourceforge.jtds.jdbc.Driver");
        Connection con = DriverManager.getConnection("jdbc:jtds:sqlserver://" + BarApplication.getInstance().mSqlServerHost + "/fastfood", BarApplication.getInstance().mSqlServerUser, BarApplication.getInstance().mSqlServerPassword);
        String sql = "SELECT " + DatabaseManager.SALE_DETAIL_BILLNO + "," +
                DatabaseManager.SALE_DETAIL_STOCKID + "," +
                DatabaseManager.SALE_DETAIL_QUANTITY + "," +
                DatabaseManager.SALE_DETAIL_OPERATEDATE + "," +
                DatabaseManager.SALE_DETAIL_PRICE + "," +
                DatabaseManager.SALE_DETAIL_COST + "," +
                DatabaseManager.SALE_DETAIL_PRICE_ORIG +
                " FROM " + DatabaseManager.SALE_DETAIL_TABLE_NAME + " WHERE " + DatabaseManager.SALE_DETAIL_BILLNO + "  like '%" + billno + "%'";
        Statement stmt = con.createStatement();//创建Statement
        ResultSet rs = stmt.executeQuery(sql);//ResultSet类似Cursor
        List<SaleDetailInfo> result = collectSaleDetailList(rs);
        stmt.close();
        if (con != null)
            con.close();
        return result;
    }

    public List<SaleDetailInfo> getSaleDetailByDate(String date) throws Exception {
        // 加载驱动程序
        Class.forName("net.sourceforge.jtds.jdbc.Driver");
        Connection con = DriverManager.getConnection("jdbc:jtds:sqlserver://" + BarApplication.getInstance().mSqlServerHost + "/fastfood", BarApplication.getInstance().mSqlServerUser, BarApplication.getInstance().mSqlServerPassword);
        String sql = "SELECT " + DatabaseManager.SALE_DETAIL_BILLNO + "," +
                DatabaseManager.SALE_DETAIL_STOCKID + "," +
                DatabaseManager.SALE_DETAIL_QUANTITY + "," +
                DatabaseManager.SALE_DETAIL_OPERATEDATE + "," +
                DatabaseManager.SALE_DETAIL_PRICE + "," +
                DatabaseManager.SALE_DETAIL_COST + "," +
                DatabaseManager.SALE_DETAIL_PRICE_ORIG +
                " FROM " + DatabaseManager.SALE_DETAIL_TABLE_NAME + " WHERE " + DatabaseManager.SALE_DETAIL_OPERATEDATE + "  > '" + date + " 00:00:00' and " + DatabaseManager.PAY_MASTER_OPERDATE + "<'" + date + " 23:59:59'";
        Statement stmt = con.createStatement();//创建Statement
        ResultSet rs = stmt.executeQuery(sql);//ResultSet类似Cursor
        List<SaleDetailInfo> result = collectSaleDetailList(rs);
        stmt.close();
        if (con != null)
            con.close();
        return result;
    }

    public List<SaleDetailInfo> getSaleDetailByStockid(String stockid) throws Exception {
        // 加载驱动程序
        Class.forName("net.sourceforge.jtds.jdbc.Driver");
        Connection con = DriverManager.getConnection("jdbc:jtds:sqlserver://" + BarApplication.getInstance().mSqlServerHost + "/fastfood", BarApplication.getInstance().mSqlServerUser, BarApplication.getInstance().mSqlServerPassword);
        String sql = "SELECT " + DatabaseManager.SALE_DETAIL_BILLNO + "," +
                DatabaseManager.SALE_DETAIL_STOCKID + "," +
                DatabaseManager.SALE_DETAIL_QUANTITY + "," +
                DatabaseManager.SALE_DETAIL_OPERATEDATE + "," +
                DatabaseManager.SALE_DETAIL_PRICE + "," +
                DatabaseManager.SALE_DETAIL_COST + "," +
                DatabaseManager.SALE_DETAIL_PRICE_ORIG +
                " FROM " + DatabaseManager.SALE_DETAIL_TABLE_NAME + " WHERE " + DatabaseManager.SALE_DETAIL_STOCKID + "  = '" + stockid + "' ORDER BY " + DatabaseManager.SALE_DETAIL_OPERATEDATE + " DESC";
        Statement stmt = con.createStatement();//创建Statement
        ResultSet rs = stmt.executeQuery(sql);//ResultSet类似Cursor
        List<SaleDetailInfo> result = collectSaleDetailList(rs);
        stmt.close();
        if (con != null)
            con.close();
        return result;
    }

    private List<MemberPointInfo> collectMemberPointList(ResultSet rs) throws Exception {
        List<MemberPointInfo> result = new ArrayList<MemberPointInfo>();
        if (rs != null) {
            while (rs.next()) {
                MemberPointInfo memberPointInfo = new MemberPointInfo();
                memberPointInfo.memberPointBillno = rs.getString(DatabaseManager.MEMBER_POINT_BILLNO);
                memberPointInfo.memberPointMemberid = rs.getString(DatabaseManager.MEMBER_POINT_MEMBERID).trim();
                memberPointInfo.memberPointOperdate = rs.getDate(DatabaseManager.MEMBER_POINT_OPERDATE);
                memberPointInfo.memberPointOpertime = rs.getTime(DatabaseManager.MEMBER_POINT_OPERDATE);
                memberPointInfo.memberPointDigest = rs.getString(DatabaseManager.MEMBER_POINT_DIGEST);
                memberPointInfo.memberPointPoint = rs.getFloat(DatabaseManager.MEMBER_POINT_POINT);
                //memberPointInfo.memberPointOrigPoint = rs.getFloat(DatabaseManager.MEMBER_POINT_ORIG_POINT);
                result.add(memberPointInfo);
            }
            rs.close();
        }
        return result;
    }

    public List<MemberPointInfo> getMemberPointByMemberId(String memberId) throws Exception {
        // 加载驱动程序
        Class.forName("net.sourceforge.jtds.jdbc.Driver");
        Connection con = DriverManager.getConnection("jdbc:jtds:sqlserver://" + BarApplication.getInstance().mSqlServerHost + "/fastfood", BarApplication.getInstance().mSqlServerUser, BarApplication.getInstance().mSqlServerPassword);
        String sql = "SELECT * FROM " + DatabaseManager.MEMBER_POINT_TABLE_NAME + " WHERE " + DatabaseManager.MEMBER_POINT_MEMBERID + "=" + memberId;
        Statement stmt = con.createStatement();//创建Statement
        ResultSet rs = stmt.executeQuery(sql);//ResultSet类似Cursor
        List<MemberPointInfo> result = collectMemberPointList(rs);
        stmt.close();
        if (con != null)
            con.close();
        return result;
    }


    /**
     * 访问Microsoft SQL Server Isszmbalv3数据库
     *
     * @author taoli
     * @since 2015-0-10 下午04:57:36
     */
    public List<Isszmbalv3SupcustInfo> getIsszmbalv3SupcustInfoList() throws Exception {
        // 加载驱动程序
        Class.forName("net.sourceforge.jtds.jdbc.Driver");
        String url = "jdbc:jtds:sqlserver://" + BarApplication.getInstance().mSqlServerHost + "/" + BarApplication.getInstance().mSqlServerDatabaseName;
        String user = BarApplication.getInstance().mSqlServerUser;
        String password = BarApplication.getInstance().mSqlServerPassword;
        Connection con = DriverManager.getConnection(url, user, password);

        String sql = "SELECT * FROM " + DatabaseManager.ISSZMBALV3_t_bd_supcust_info_TABLE_NAME;
        Statement stmt = con.createStatement();//创建Statement
        ResultSet rs = stmt.executeQuery(sql);//ResultSet类似Cursor

        List<Isszmbalv3SupcustInfo> isszmbalv3SupcustInfoList = new ArrayList<Isszmbalv3SupcustInfo>();
        if (rs != null) {
            while (rs.next()) {
                Isszmbalv3SupcustInfo item = new Isszmbalv3SupcustInfo();
                item.supcust_no = rs.getString(DatabaseManager.ISSZMBALV3_t_bd_supcust_info_supcust_no);
                item.sup_name = rs.getString(DatabaseManager.ISSZMBALV3_t_bd_supcust_info_sup_name);
                item.sup_type = rs.getString(DatabaseManager.ISSZMBALV3_t_bd_supcust_info_sup_type);
                item.supcust_flag = rs.getString(DatabaseManager.ISSZMBALV3_t_bd_supcust_info_supcust_flag);
                isszmbalv3SupcustInfoList.add(item);
            }
            rs.close();
        }

        stmt.close();
        if (con != null)
            con.close();
        return isszmbalv3SupcustInfoList;
    }

    public long insertIsszmbalv3SupcustInfo(ContentValues values) throws Exception {
        long row = 0;
        // 加载驱动程序
        Class.forName("net.sourceforge.jtds.jdbc.Driver");
        Connection con = DriverManager.getConnection("jdbc:jtds:sqlserver://" + BarApplication.getInstance().mSqlServerHost + "/" + BarApplication.getInstance().mSqlServerDatabaseName, BarApplication.getInstance().mSqlServerUser, BarApplication.getInstance().mSqlServerPassword);
        Statement stmt = con.createStatement();//创建Statement
        String sql = "insert into " + DatabaseManager.ISSZMBALV3_t_bd_supcust_info_TABLE_NAME + "(" +
                DatabaseManager.ISSZMBALV3_t_bd_supcust_info_supcust_no + "," +
                DatabaseManager.ISSZMBALV3_t_bd_supcust_info_sup_name + "," +
                DatabaseManager.ISSZMBALV3_t_bd_supcust_info_sup_type + "," +
                DatabaseManager.ISSZMBALV3_t_bd_supcust_info_supcust_flag + ") values" + "('" +
                values.get(DatabaseManager.ISSZMBALV3_t_bd_supcust_info_supcust_no) + "','" +
                values.get(DatabaseManager.ISSZMBALV3_t_bd_supcust_info_sup_name) + "','" +
                values.get(DatabaseManager.ISSZMBALV3_t_bd_supcust_info_sup_type) + "','" +
                values.get(DatabaseManager.ISSZMBALV3_t_bd_supcust_info_supcust_flag) + "')";
        stmt.executeUpdate(sql);//ResultSet类似Cursor
        row = stmt.getUpdateCount();
        stmt.close();
        if (con != null)
            con.close();
        return row;
    }

    public long updateIsszmbalv3SupcustInfo(ContentValues values) throws Exception {
        long row = 0;
        // 加载驱动程序
        Class.forName("net.sourceforge.jtds.jdbc.Driver");
        Connection con = DriverManager.getConnection("jdbc:jtds:sqlserver://" + BarApplication.getInstance().mSqlServerHost + "/" + BarApplication.getInstance().mSqlServerDatabaseName, BarApplication.getInstance().mSqlServerUser, BarApplication.getInstance().mSqlServerPassword);
        String sql = "update " + DatabaseManager.ISSZMBALV3_t_bd_supcust_info_TABLE_NAME + " set " +
                DatabaseManager.ISSZMBALV3_t_bd_supcust_info_sup_name + "='" + values.get(DatabaseManager.ISSZMBALV3_t_bd_supcust_info_sup_name) + "' " +
                " WHERE " + DatabaseManager.ISSZMBALV3_t_bd_supcust_info_supcust_no + "='" + values.get(DatabaseManager.ISSZMBALV3_t_bd_supcust_info_supcust_no) + "'";
        Statement stmt = con.createStatement();//创建Statement
        stmt.executeUpdate(sql);//ResultSet类似Cursor
        row = stmt.getUpdateCount();
        stmt.close();
        if (con != null)
            con.close();
        return row;
    }

    public Map<String, String> getIsszmbalv3AllStockType() throws Exception {
        // 加载驱动程序
        Class.forName("net.sourceforge.jtds.jdbc.Driver");
        String url = "jdbc:jtds:sqlserver://" + BarApplication.getInstance().mSqlServerHost + "/" + BarApplication.getInstance().mSqlServerDatabaseName;
        String user = BarApplication.getInstance().mSqlServerUser;
        String password = BarApplication.getInstance().mSqlServerPassword;
        Connection con = DriverManager.getConnection(url, user, password);

        String sql = "SELECT * FROM " + DatabaseManager.ISSZMBALV3_ITEM_CLS_TABLE_NAME;
        Statement stmt = con.createStatement();//创建Statement
        ResultSet rs = stmt.executeQuery(sql);//ResultSet类似Cursor

        Map<String, String> isszmbalv3StockTypeMap = new TreeMap<String, String>();
        isszmbalv3StockTypeMap.put("-1", "全部");
        if (rs != null) {
            while (rs.next()) {
                isszmbalv3StockTypeMap.put(rs.getString(DatabaseManager.ISSZMBALV3_ITEM_CLS_ITEM_CLSNO), rs.getString(DatabaseManager.ISSZMBALV3_ITEM_CLS_ITEM_CLSNAME));
            }
            rs.close();
        }

        stmt.close();
        if (con != null)
            con.close();
        return isszmbalv3StockTypeMap;
    }

    public String getIsszmbalv3MaxDispersionIndex() throws Exception {
        // 加载驱动程序
        Class.forName("net.sourceforge.jtds.jdbc.Driver");
        Connection con = DriverManager.getConnection("jdbc:jtds:sqlserver://" + BarApplication.getInstance().mSqlServerHost + "/" + BarApplication.getInstance().mSqlServerDatabaseName, BarApplication.getInstance().mSqlServerUser, BarApplication.getInstance().mSqlServerPassword);
        String sql = "select max ( " + DatabaseManager.ISSZMBALV3_t_bd_item_info_item_no + " ) from " + DatabaseManager.ISSZMBALV3_t_bd_item_info_TABLE_NAME +
                " where " + DatabaseManager.ISSZMBALV3_t_bd_item_info_item_no + " < '09000' and " + DatabaseManager.ISSZMBALV3_t_bd_item_measure_flag + " = '1'";
        String str = "";
        try {
            Statement stmt = con.createStatement();//创建Statement
            ResultSet rs = stmt.executeQuery(sql);//ResultSet类似Cursor
            String item_no_max_str = "";
            if (rs.next()) {
                item_no_max_str = rs.getString(1).trim();
            }
            int item_no_max_int = Integer.parseInt(item_no_max_str) + 1;
            str = String.format("%5d", item_no_max_int).replace(" ", "0");    //补满5位String型数据

            rs.close();
            stmt.close();
        } catch (Exception e) {
            e.printStackTrace();
        }

        if (con != null)
            con.close();
        return str;
    }

    public int getIsszmbalv3StockCount(String typeid) throws Exception {
        // 加载驱动程序
        Class.forName("net.sourceforge.jtds.jdbc.Driver");
        Connection con = DriverManager.getConnection("jdbc:jtds:sqlserver://" + BarApplication.getInstance().mSqlServerHost + "/" + BarApplication.getInstance().mSqlServerDatabaseName, BarApplication.getInstance().mSqlServerUser, BarApplication.getInstance().mSqlServerPassword);
        String sql = "";
        if (!typeid.equals("-1"))
            sql = "select count(1) as totalCount from " + DatabaseManager.ISSZMBALV3_t_bd_item_info_TABLE_NAME + " WHERE " + DatabaseManager.ISSZMBALV3_t_bd_item_info_item_clsno + "='" + typeid + "'";
        else
            sql = "select count(1) as totalCount from " + DatabaseManager.ISSZMBALV3_t_bd_item_info_TABLE_NAME;

        int totalCount = -1;
        try {
            Statement stmt = con.createStatement();//创建Statement
            ResultSet rs = stmt.executeQuery(sql);//ResultSet类似Cursor
            if (rs.next()) {
                totalCount = rs.getInt(1);
            }
            rs.close();
            stmt.close();
        } catch (Exception e) {
            e.printStackTrace();
        }

        if (con != null)
            con.close();
        return totalCount;
    }

    private List<Isszmbalv3StockInfo> collectIsszmbalv3StockInfoList(ResultSet rs) throws Exception {
        List<Isszmbalv3StockInfo> result = new ArrayList<Isszmbalv3StockInfo>();
        if (rs != null) {
            while (rs.next()) {
                Isszmbalv3StockInfo isszmbalv3StockInfo = new Isszmbalv3StockInfo();
                isszmbalv3StockInfo.item_no = rs.getString(DatabaseManager.ISSZMBALV3_t_bd_item_info_item_no);
                isszmbalv3StockInfo.item_name = rs.getString(DatabaseManager.ISSZMBALV3_t_bd_item_info_item_name);
                isszmbalv3StockInfo.item_subname = rs.getString(DatabaseManager.ISSZMBALV3_t_bd_item_info_item_subname);
                isszmbalv3StockInfo.item_clsno = rs.getString(DatabaseManager.ISSZMBALV3_t_bd_item_info_item_clsno);
                isszmbalv3StockInfo.item_rem = rs.getString(DatabaseManager.ISSZMBALV3_t_bd_item_item_rem);
                isszmbalv3StockInfo.price = rs.getFloat(DatabaseManager.ISSZMBALV3_t_bd_item_price);
                isszmbalv3StockInfo.sale_price = rs.getFloat(DatabaseManager.ISSZMBALV3_t_bd_item_sale_price);
                isszmbalv3StockInfo.measure_flag = rs.getString(DatabaseManager.ISSZMBALV3_t_bd_item_measure_flag);
                isszmbalv3StockInfo.new_oper_id = rs.getString(DatabaseManager.ISSZMBALV3_t_bd_item_new_oper_id);
                isszmbalv3StockInfo.modify_oper_id = rs.getString(DatabaseManager.ISSZMBALV3_t_bd_item_modify_oper_id);
                isszmbalv3StockInfo.vip_acc_flag = rs.getString(DatabaseManager.ISSZMBALV3_t_bd_item_vip_acc_flag);
                isszmbalv3StockInfo.main_supcust = rs.getString(DatabaseManager.ISSZMBALV3_t_bd_item_main_supcust);
                result.add(isszmbalv3StockInfo);
            }
            rs.close();
        }
        return result;
    }

    public List<Isszmbalv3StockInfo> getIsszmbalv3StockByName(String stockName, int sortMode) throws Exception {
        // 加载驱动程序
        Class.forName("net.sourceforge.jtds.jdbc.Driver");
        Connection con = DriverManager.getConnection("jdbc:jtds:sqlserver://" + BarApplication.getInstance().mSqlServerHost + "/" + BarApplication.getInstance().mSqlServerDatabaseName, BarApplication.getInstance().mSqlServerUser, BarApplication.getInstance().mSqlServerPassword);
        String sortName = DatabaseManager.ISSZMBALV3_t_bd_item_item_rem;
        String sortUpOrDown0 = " ASC ";
        String sortUpOrDown1 = " DESC ";
        switch (sortMode) {
            case 0:
                sortName = DatabaseManager.ISSZMBALV3_t_bd_item_item_rem;
                sortUpOrDown0 = " ASC ";
                sortUpOrDown1 = " DESC ";
                break;
            case 1:
                sortName = DatabaseManager.ISSZMBALV3_t_bd_item_item_rem;
                sortUpOrDown0 = " DESC ";
                sortUpOrDown1 = " ASC ";
                break;
            case 2:
                sortName = DatabaseManager.ISSZMBALV3_t_bd_item_info_item_no;
                sortUpOrDown0 = " ASC ";
                sortUpOrDown1 = " DESC ";
                break;
            case 3:
                sortName = DatabaseManager.ISSZMBALV3_t_bd_item_info_item_no;
                sortUpOrDown0 = " DESC ";
                sortUpOrDown1 = " ASC ";
                break;
            case 4:
                sortName = DatabaseManager.ISSZMBALV3_t_bd_item_sale_price;
                sortUpOrDown0 = " ASC ";
                sortUpOrDown1 = " DESC ";
                break;
            case 5:
                sortName = DatabaseManager.ISSZMBALV3_t_bd_item_sale_price;
                sortUpOrDown0 = " DESC ";
                sortUpOrDown1 = " ASC ";
                break;
        }
        String sql = "SELECT " + DatabaseManager.ISSZMBALV3_t_bd_item_info_item_no + "," +
                DatabaseManager.ISSZMBALV3_t_bd_item_info_item_name + "," +
                DatabaseManager.ISSZMBALV3_t_bd_item_info_item_subname + "," +
                DatabaseManager.ISSZMBALV3_t_bd_item_info_item_clsno + "," +
                DatabaseManager.ISSZMBALV3_t_bd_item_item_rem + "," +
                DatabaseManager.ISSZMBALV3_t_bd_item_measure_flag + "," +
                DatabaseManager.ISSZMBALV3_t_bd_item_new_oper_id + "," +
                DatabaseManager.ISSZMBALV3_t_bd_item_modify_oper_id + "," +
                DatabaseManager.ISSZMBALV3_t_bd_item_vip_acc_flag + "," +
                DatabaseManager.ISSZMBALV3_t_bd_item_price + "," +
                DatabaseManager.ISSZMBALV3_t_bd_item_sale_price + "," +
                DatabaseManager.ISSZMBALV3_t_bd_item_main_supcust +
                " FROM " + DatabaseManager.ISSZMBALV3_t_bd_item_info_TABLE_NAME + " WHERE " + DatabaseManager.ISSZMBALV3_t_bd_item_info_item_name + "  like '%" + stockName + "%'" + " ORDER BY " + sortName + sortUpOrDown0;
        Statement stmt = con.createStatement();//创建Statement
        ResultSet rs = stmt.executeQuery(sql);//ResultSet类似Cursor
        List<Isszmbalv3StockInfo> result = collectIsszmbalv3StockInfoList(rs);
        stmt.close();
        if (con != null)
            con.close();
        return result;
    }

    public List<Isszmbalv3StockInfo> getIsszmbalv3StockBySpell(String stockSpell, int sortMode) throws Exception {
        // 加载驱动程序
        Class.forName("net.sourceforge.jtds.jdbc.Driver");
        Connection con = DriverManager.getConnection("jdbc:jtds:sqlserver://" + BarApplication.getInstance().mSqlServerHost + "/" + BarApplication.getInstance().mSqlServerDatabaseName, BarApplication.getInstance().mSqlServerUser, BarApplication.getInstance().mSqlServerPassword);
        String sortName = DatabaseManager.ISSZMBALV3_t_bd_item_item_rem;
        String sortUpOrDown0 = " ASC ";
        String sortUpOrDown1 = " DESC ";
        switch (sortMode) {
            case 0:
                sortName = DatabaseManager.ISSZMBALV3_t_bd_item_item_rem;
                sortUpOrDown0 = " ASC ";
                sortUpOrDown1 = " DESC ";
                break;
            case 1:
                sortName = DatabaseManager.ISSZMBALV3_t_bd_item_item_rem;
                sortUpOrDown0 = " DESC ";
                sortUpOrDown1 = " ASC ";
                break;
            case 2:
                sortName = DatabaseManager.ISSZMBALV3_t_bd_item_info_item_no;
                sortUpOrDown0 = " ASC ";
                sortUpOrDown1 = " DESC ";
                break;
            case 3:
                sortName = DatabaseManager.ISSZMBALV3_t_bd_item_info_item_no;
                sortUpOrDown0 = " DESC ";
                sortUpOrDown1 = " ASC ";
                break;
            case 4:
                sortName = DatabaseManager.ISSZMBALV3_t_bd_item_sale_price;
                sortUpOrDown0 = " ASC ";
                sortUpOrDown1 = " DESC ";
                break;
            case 5:
                sortName = DatabaseManager.ISSZMBALV3_t_bd_item_sale_price;
                sortUpOrDown0 = " DESC ";
                sortUpOrDown1 = " ASC ";
                break;
        }
        String sql = "SELECT " + DatabaseManager.ISSZMBALV3_t_bd_item_info_item_no + "," +
                DatabaseManager.ISSZMBALV3_t_bd_item_info_item_name + "," +
                DatabaseManager.ISSZMBALV3_t_bd_item_info_item_subname + "," +
                DatabaseManager.ISSZMBALV3_t_bd_item_info_item_clsno + "," +
                DatabaseManager.ISSZMBALV3_t_bd_item_item_rem + "," +
                DatabaseManager.ISSZMBALV3_t_bd_item_measure_flag + "," +
                DatabaseManager.ISSZMBALV3_t_bd_item_new_oper_id + "," +
                DatabaseManager.ISSZMBALV3_t_bd_item_modify_oper_id + "," +
                DatabaseManager.ISSZMBALV3_t_bd_item_vip_acc_flag + "," +
                DatabaseManager.ISSZMBALV3_t_bd_item_price + "," +
                DatabaseManager.ISSZMBALV3_t_bd_item_sale_price + "," +
                DatabaseManager.ISSZMBALV3_t_bd_item_main_supcust +
                " FROM " + DatabaseManager.ISSZMBALV3_t_bd_item_info_TABLE_NAME + " WHERE " + DatabaseManager.ISSZMBALV3_t_bd_item_item_rem + "  like '%" + stockSpell + "%'" + " ORDER BY " + sortName + sortUpOrDown0;
        ;
        Statement stmt = con.createStatement();//创建Statement
        ResultSet rs = stmt.executeQuery(sql);//ResultSet类似Cursor
        List<Isszmbalv3StockInfo> result = collectIsszmbalv3StockInfoList(rs);
        stmt.close();
        if (con != null)
            con.close();
        return result;
    }

    public List<Isszmbalv3StockInfo> getIsszmbalv3StockByNo(String stockNo, int sortMode) throws Exception {
        // 加载驱动程序
        Class.forName("net.sourceforge.jtds.jdbc.Driver");
        Connection con = DriverManager.getConnection("jdbc:jtds:sqlserver://" + BarApplication.getInstance().mSqlServerHost + "/" + BarApplication.getInstance().mSqlServerDatabaseName, BarApplication.getInstance().mSqlServerUser, BarApplication.getInstance().mSqlServerPassword);
        String sortName = DatabaseManager.ISSZMBALV3_t_bd_item_item_rem;
        String sortUpOrDown0 = " ASC ";
        String sortUpOrDown1 = " DESC ";
        switch (sortMode) {
            case 0:
                sortName = DatabaseManager.ISSZMBALV3_t_bd_item_item_rem;
                sortUpOrDown0 = " ASC ";
                sortUpOrDown1 = " DESC ";
                break;
            case 1:
                sortName = DatabaseManager.ISSZMBALV3_t_bd_item_item_rem;
                sortUpOrDown0 = " DESC ";
                sortUpOrDown1 = " ASC ";
                break;
            case 2:
                sortName = DatabaseManager.ISSZMBALV3_t_bd_item_info_item_no;
                sortUpOrDown0 = " ASC ";
                sortUpOrDown1 = " DESC ";
                break;
            case 3:
                sortName = DatabaseManager.ISSZMBALV3_t_bd_item_info_item_no;
                sortUpOrDown0 = " DESC ";
                sortUpOrDown1 = " ASC ";
                break;
            case 4:
                sortName = DatabaseManager.ISSZMBALV3_t_bd_item_sale_price;
                sortUpOrDown0 = " ASC ";
                sortUpOrDown1 = " DESC ";
                break;
            case 5:
                sortName = DatabaseManager.ISSZMBALV3_t_bd_item_sale_price;
                sortUpOrDown0 = " DESC ";
                sortUpOrDown1 = " ASC ";
                break;
        }
        String sql = "SELECT " + DatabaseManager.ISSZMBALV3_t_bd_item_info_item_no + "," +
                DatabaseManager.ISSZMBALV3_t_bd_item_info_item_name + "," +
                DatabaseManager.ISSZMBALV3_t_bd_item_info_item_subname + "," +
                DatabaseManager.ISSZMBALV3_t_bd_item_info_item_clsno + "," +
                DatabaseManager.ISSZMBALV3_t_bd_item_item_rem + "," +
                DatabaseManager.ISSZMBALV3_t_bd_item_measure_flag + "," +
                DatabaseManager.ISSZMBALV3_t_bd_item_new_oper_id + "," +
                DatabaseManager.ISSZMBALV3_t_bd_item_modify_oper_id + "," +
                DatabaseManager.ISSZMBALV3_t_bd_item_vip_acc_flag + "," +
                DatabaseManager.ISSZMBALV3_t_bd_item_price + "," +
                DatabaseManager.ISSZMBALV3_t_bd_item_sale_price + "," +
                DatabaseManager.ISSZMBALV3_t_bd_item_main_supcust +
                " FROM " + DatabaseManager.ISSZMBALV3_t_bd_item_info_TABLE_NAME + " WHERE " + DatabaseManager.ISSZMBALV3_t_bd_item_info_item_no + "  like '%" + stockNo + "%'" + " ORDER BY " + sortName + sortUpOrDown0;
        ;
        Statement stmt = con.createStatement();//创建Statement
        ResultSet rs = stmt.executeQuery(sql);//ResultSet类似Cursor
        List<Isszmbalv3StockInfo> result = collectIsszmbalv3StockInfoList(rs);
        stmt.close();
        if (con != null)
            con.close();
        return result;
    }

    public List<Isszmbalv3StockInfo> getIsszmbalv3StockByNo1(String stockNo) throws Exception {
        // 加载驱动程序
        Class.forName("net.sourceforge.jtds.jdbc.Driver");
        Connection con = DriverManager.getConnection("jdbc:jtds:sqlserver://" + BarApplication.getInstance().mSqlServerHost + "/" + BarApplication.getInstance().mSqlServerDatabaseName, BarApplication.getInstance().mSqlServerUser, BarApplication.getInstance().mSqlServerPassword);
        String sql = "SELECT " + DatabaseManager.ISSZMBALV3_t_bd_item_info_item_no + "," +
                DatabaseManager.ISSZMBALV3_t_bd_item_info_item_name + "," +
                DatabaseManager.ISSZMBALV3_t_bd_item_info_item_subname + "," +
                DatabaseManager.ISSZMBALV3_t_bd_item_info_item_clsno + "," +
                DatabaseManager.ISSZMBALV3_t_bd_item_item_rem + "," +
                DatabaseManager.ISSZMBALV3_t_bd_item_measure_flag + "," +
                DatabaseManager.ISSZMBALV3_t_bd_item_new_oper_id + "," +
                DatabaseManager.ISSZMBALV3_t_bd_item_modify_oper_id + "," +
                DatabaseManager.ISSZMBALV3_t_bd_item_vip_acc_flag + "," +
                DatabaseManager.ISSZMBALV3_t_bd_item_price + "," +
                DatabaseManager.ISSZMBALV3_t_bd_item_sale_price + "," +
                DatabaseManager.ISSZMBALV3_t_bd_item_main_supcust +
                " FROM " + DatabaseManager.ISSZMBALV3_t_bd_item_info_TABLE_NAME + " WHERE " + DatabaseManager.ISSZMBALV3_t_bd_item_info_item_no + "  ='" + stockNo + "'";
        Statement stmt = con.createStatement();//创建Statement
        ResultSet rs = stmt.executeQuery(sql);//ResultSet类似Cursor
        List<Isszmbalv3StockInfo> result = collectIsszmbalv3StockInfoList(rs);
        stmt.close();
        if (con != null)
            con.close();
        return result;
    }

    public List<Isszmbalv3StockInfo> getIsszmbalv3StockByTypeId(String stockTypeId, int startIndex, int pageSize, int maxCount, int sortMode) throws Exception {
        // 加载驱动程序
        Class.forName("net.sourceforge.jtds.jdbc.Driver");
        Connection con = DriverManager.getConnection("jdbc:jtds:sqlserver://" + BarApplication.getInstance().mSqlServerHost + "/" + BarApplication.getInstance().mSqlServerDatabaseName, BarApplication.getInstance().mSqlServerUser, BarApplication.getInstance().mSqlServerPassword);
        String sortName = DatabaseManager.ISSZMBALV3_t_bd_item_item_rem;
        String sortUpOrDown0 = " ASC ";
        String sortUpOrDown1 = " DESC ";
        switch (sortMode) {
            case 0:
                sortName = DatabaseManager.ISSZMBALV3_t_bd_item_item_rem;
                sortUpOrDown0 = " ASC ";
                sortUpOrDown1 = " DESC ";
                break;
            case 1:
                sortName = DatabaseManager.ISSZMBALV3_t_bd_item_item_rem;
                sortUpOrDown0 = " DESC ";
                sortUpOrDown1 = " ASC ";
                break;
            case 2:
                sortName = DatabaseManager.ISSZMBALV3_t_bd_item_info_item_no;
                sortUpOrDown0 = " ASC ";
                sortUpOrDown1 = " DESC ";
                break;
            case 3:
                sortName = DatabaseManager.ISSZMBALV3_t_bd_item_info_item_no;
                sortUpOrDown0 = " DESC ";
                sortUpOrDown1 = " ASC ";
                break;
            case 4:
                sortName = DatabaseManager.ISSZMBALV3_t_bd_item_sale_price;
                sortUpOrDown0 = " ASC ";
                sortUpOrDown1 = " DESC ";
                break;
            case 5:
                sortName = DatabaseManager.ISSZMBALV3_t_bd_item_sale_price;
                sortUpOrDown0 = " DESC ";
                sortUpOrDown1 = " ASC ";
                break;
        }
        String sql = "";
        if (pageSize == -1) {
            sql = "SELECT " + DatabaseManager.ISSZMBALV3_t_bd_item_info_item_no + "," +
                    DatabaseManager.ISSZMBALV3_t_bd_item_info_item_name + "," +
                    DatabaseManager.ISSZMBALV3_t_bd_item_info_item_subname + "," +
                    DatabaseManager.ISSZMBALV3_t_bd_item_info_item_clsno + "," +
                    DatabaseManager.ISSZMBALV3_t_bd_item_item_rem + "," +
                    DatabaseManager.ISSZMBALV3_t_bd_item_measure_flag + "," +
                    DatabaseManager.ISSZMBALV3_t_bd_item_new_oper_id + "," +
                    DatabaseManager.ISSZMBALV3_t_bd_item_modify_oper_id + "," +
                    DatabaseManager.ISSZMBALV3_t_bd_item_vip_acc_flag + "," +
                    DatabaseManager.ISSZMBALV3_t_bd_item_price + "," +
                    DatabaseManager.ISSZMBALV3_t_bd_item_sale_price + "," +
                    DatabaseManager.ISSZMBALV3_t_bd_item_main_supcust +
                    " FROM " + DatabaseManager.ISSZMBALV3_t_bd_item_info_TABLE_NAME + " WHERE " + DatabaseManager.ISSZMBALV3_t_bd_item_info_item_clsno + "='" + stockTypeId + "'" + " ORDER BY " + sortName + sortUpOrDown0;
            ;
        } else {
            int endIndex = startIndex + pageSize;
            if (endIndex > maxCount) {
                pageSize = pageSize - (endIndex - maxCount);
            }
            sql = "SELECT * FROM " + DatabaseManager.ISSZMBALV3_t_bd_item_info_TABLE_NAME + " w1 " +
                    " WHERE " + sortName + " IN ( " +
                    "SELECT top " + pageSize + " " + sortName + " FROM " +
                    "( " +
                    "SELECT top " + endIndex + " " + sortName + " FROM " + DatabaseManager.ISSZMBALV3_t_bd_item_info_TABLE_NAME +
                    " WHERE " + DatabaseManager.ISSZMBALV3_t_bd_item_info_item_clsno + "='" + stockTypeId + "' ORDER BY " + sortName + sortUpOrDown0 +
                    ") w ORDER BY w." + sortName + sortUpOrDown1 +
                    ") " +
                    " ORDER BY w1." + sortName + sortUpOrDown0;
        }

        List<Isszmbalv3StockInfo> result = null;
        try {
            Statement stmt = con.createStatement();//创建Statement
            ResultSet rs = stmt.executeQuery(sql);//ResultSet类似Cursor
            result = collectIsszmbalv3StockInfoList(rs);
            stmt.close();
        } catch (Exception e) {
            e.printStackTrace();
        }

        if (con != null)
            con.close();
        return result;
    }

    // SortMode = -1;     // 0:名字升序   1：名字降序    2：ID升序  3：ID降序    4：价格升序     5：价格降序
    public List<Isszmbalv3StockInfo> getAllIsszmbalv3Stock(int startIndex, int pageSize, int maxCount, int sortMode) throws Exception {
        // 加载驱动程序
        Class.forName("net.sourceforge.jtds.jdbc.Driver");
        Connection con = DriverManager.getConnection("jdbc:jtds:sqlserver://" + BarApplication.getInstance().mSqlServerHost + "/" + BarApplication.getInstance().mSqlServerDatabaseName, BarApplication.getInstance().mSqlServerUser, BarApplication.getInstance().mSqlServerPassword);
        String sql = "";
        String sortName = DatabaseManager.ISSZMBALV3_t_bd_item_item_rem;
        String sortUpOrDown0 = " ASC ";
        String sortUpOrDown1 = " DESC ";
        switch (sortMode) {
            case 0:
                sortName = DatabaseManager.ISSZMBALV3_t_bd_item_item_rem;
                sortUpOrDown0 = " ASC ";
                sortUpOrDown1 = " DESC ";
                break;
            case 1:
                sortName = DatabaseManager.ISSZMBALV3_t_bd_item_item_rem;
                sortUpOrDown0 = " DESC ";
                sortUpOrDown1 = " ASC ";
                break;
            case 2:
                sortName = DatabaseManager.ISSZMBALV3_t_bd_item_info_item_no;
                sortUpOrDown0 = " ASC ";
                sortUpOrDown1 = " DESC ";
                break;
            case 3:
                sortName = DatabaseManager.ISSZMBALV3_t_bd_item_info_item_no;
                sortUpOrDown0 = " DESC ";
                sortUpOrDown1 = " ASC ";
                break;
            case 4:
                sortName = DatabaseManager.ISSZMBALV3_t_bd_item_sale_price;
                sortUpOrDown0 = " ASC ";
                sortUpOrDown1 = " DESC ";
                break;
            case 5:
                sortName = DatabaseManager.ISSZMBALV3_t_bd_item_sale_price;
                sortUpOrDown0 = " DESC ";
                sortUpOrDown1 = " ASC ";
                break;
        }
        if (pageSize == -1) {
            sql = "SELECT " + DatabaseManager.ISSZMBALV3_t_bd_item_info_item_no + "," +
                    DatabaseManager.ISSZMBALV3_t_bd_item_info_item_name + "," +
                    DatabaseManager.ISSZMBALV3_t_bd_item_info_item_subname + "," +
                    DatabaseManager.ISSZMBALV3_t_bd_item_info_item_clsno + "," +
                    DatabaseManager.ISSZMBALV3_t_bd_item_item_rem + "," +
                    DatabaseManager.ISSZMBALV3_t_bd_item_measure_flag + "," +
                    DatabaseManager.ISSZMBALV3_t_bd_item_new_oper_id + "," +
                    DatabaseManager.ISSZMBALV3_t_bd_item_modify_oper_id + "," +
                    DatabaseManager.ISSZMBALV3_t_bd_item_vip_acc_flag + "," +
                    DatabaseManager.ISSZMBALV3_t_bd_item_price + "," +
                    DatabaseManager.ISSZMBALV3_t_bd_item_sale_price + "," +
                    DatabaseManager.ISSZMBALV3_t_bd_item_main_supcust +
                    " FROM " + DatabaseManager.ISSZMBALV3_t_bd_item_info_TABLE_NAME + " ORDER BY " + sortName + sortUpOrDown0;
        } else {
            int endIndex = startIndex + pageSize;
            if (endIndex > maxCount) {
                pageSize = pageSize - (endIndex - maxCount);
            }
            sql = "SELECT * FROM " + DatabaseManager.ISSZMBALV3_t_bd_item_info_TABLE_NAME + " w1 " +
                    " WHERE " + sortName + " IN ( " +
                    "SELECT top " + pageSize + " " + sortName + " FROM " +
                    "( " +
                    "SELECT top " + endIndex + " " + sortName + " FROM " + DatabaseManager.ISSZMBALV3_t_bd_item_info_TABLE_NAME + " ORDER BY " + sortName + sortUpOrDown0 +
                    ") w ORDER BY w." + sortName + sortUpOrDown1 +
                    ") " +
                    " ORDER BY w1." + sortName + sortUpOrDown0;
            //sql="select * from ( select top 10 * from ( select top 10 * from stock order by stockid desc ) a order by stockid asc ) b order by stockid desc";
        }

        List<Isszmbalv3StockInfo> result = null;
        try {
            Statement stmt = con.createStatement();//创建Statement
            ResultSet rs = stmt.executeQuery(sql);//ResultSet类似Cursor
            result = collectIsszmbalv3StockInfoList(rs);
            stmt.close();
        } catch (Exception e) {
            e.printStackTrace();
        }

        if (con != null)
            con.close();
        return result;
    }

    public long insertIsszmbalv3StockInfo(ContentValues values) throws Exception {
        long row = 0;
        // 加载驱动程序
        Class.forName("net.sourceforge.jtds.jdbc.Driver");
        Connection con = DriverManager.getConnection("jdbc:jtds:sqlserver://" + BarApplication.getInstance().mSqlServerHost + "/" + BarApplication.getInstance().mSqlServerDatabaseName, BarApplication.getInstance().mSqlServerUser, BarApplication.getInstance().mSqlServerPassword);
        Statement stmt = con.createStatement();//创建Statement
        String sql = "";
        if (values.containsKey(DatabaseManager.ISSZMBALV3_t_bd_item_new_oper_id)) {
            sql = "insert into " + DatabaseManager.ISSZMBALV3_t_bd_item_info_TABLE_NAME + "(" +
                    DatabaseManager.ISSZMBALV3_t_bd_item_info_item_no + "," +
                    DatabaseManager.ISSZMBALV3_t_bd_item_info_item_subno + "," +
                    DatabaseManager.ISSZMBALV3_t_bd_item_info_item_name + "," +
                    DatabaseManager.ISSZMBALV3_t_bd_item_info_item_subname + "," +
                    DatabaseManager.ISSZMBALV3_t_bd_item_info_item_clsno + "," +
                    DatabaseManager.ISSZMBALV3_t_bd_item_info_item_brand + "," +
                    DatabaseManager.ISSZMBALV3_t_bd_item_info_item_brandname + "," +
                    DatabaseManager.ISSZMBALV3_t_bd_item_price + "," +
                    DatabaseManager.ISSZMBALV3_t_bd_item_base_price + "," +
                    DatabaseManager.ISSZMBALV3_t_bd_item_sale_price + "," +
                    DatabaseManager.ISSZMBALV3_t_bd_item_combine_sta + "," +
                    DatabaseManager.ISSZMBALV3_t_bd_item_status + "," +
                    DatabaseManager.ISSZMBALV3_t_bd_item_display_flag + "," +
                    DatabaseManager.ISSZMBALV3_t_bd_item_po_cycle + "," +
                    DatabaseManager.ISSZMBALV3_t_bd_item_so_cycle + "," +
                    DatabaseManager.ISSZMBALV3_t_bd_item_automin_flag + "," +
                    DatabaseManager.ISSZMBALV3_t_bd_item_en_dis + "," +
                    DatabaseManager.ISSZMBALV3_t_bd_item_change_price + "," +
                    DatabaseManager.ISSZMBALV3_t_bd_item_purchase_tax + "," +
                    DatabaseManager.ISSZMBALV3_t_bd_item_sale_tax + "," +
                    DatabaseManager.ISSZMBALV3_t_bd_item_purchase_spec + "," +
                    DatabaseManager.ISSZMBALV3_t_bd_item_shipment_spec + "," +
                    DatabaseManager.ISSZMBALV3_t_bd_item_main_supcust + "," +
                    DatabaseManager.ISSZMBALV3_t_bd_item_item_stock + "," +
                    DatabaseManager.ISSZMBALV3_t_bd_item_build_date + "," +
                    DatabaseManager.ISSZMBALV3_t_bd_item_abc + "," +
                    DatabaseManager.ISSZMBALV3_t_bd_item_branch_price + "," +
                    DatabaseManager.ISSZMBALV3_t_bd_item_item_rem + "," +
                    DatabaseManager.ISSZMBALV3_t_bd_item_vip_price + "," +
                    DatabaseManager.ISSZMBALV3_t_bd_item_sale_min_price + "," +
                    DatabaseManager.ISSZMBALV3_t_bd_item_com_flag + "," +
                    DatabaseManager.ISSZMBALV3_t_bd_item_vip_acc_flag + "," +
                    DatabaseManager.ISSZMBALV3_t_bd_item_vip_acc_num + "," +
                    DatabaseManager.ISSZMBALV3_t_bd_item_dpfm_type + "," +
                    DatabaseManager.ISSZMBALV3_t_bd_item_return_rate + "," +
                    DatabaseManager.ISSZMBALV3_t_bd_item_sale_flag + "," +
                    DatabaseManager.ISSZMBALV3_t_bd_item_scheme_price + "," +
                    DatabaseManager.ISSZMBALV3_t_bd_item_vip_price2 + "," +
                    DatabaseManager.ISSZMBALV3_t_bd_item_vip_price3 + "," +
                    DatabaseManager.ISSZMBALV3_t_bd_item_vip_price4 + "," +
                    DatabaseManager.ISSZMBALV3_t_bd_item_vip_price5 + "," +
                    DatabaseManager.ISSZMBALV3_t_bd_item_valid_days + "," +
                    DatabaseManager.ISSZMBALV3_t_bd_item_item_com + "," +
                    DatabaseManager.ISSZMBALV3_t_bd_item_measure_flag + "," +
                    DatabaseManager.ISSZMBALV3_t_bd_item_item_sup_flag + "," +
                    DatabaseManager.ISSZMBALV3_t_bd_item_sup_rate + "," +
                    DatabaseManager.ISSZMBALV3_t_bd_item_new_oper_id + "," +
                    DatabaseManager.ISSZMBALV3_t_bd_item_modify_oper_id + "," +
                    DatabaseManager.ISSZMBALV3_t_bd_item_en_gift + "," +
                    DatabaseManager.ISSZMBALV3_t_bd_item_promote_flag + "," +
                    DatabaseManager.ISSZMBALV3_t_bd_item_allow_flag + "," +
                    DatabaseManager.ISSZMBALV3_t_bd_item_hand_flag + ") values" + "('" +
                    values.get(DatabaseManager.ISSZMBALV3_t_bd_item_info_item_no) + "','" +
                    values.get(DatabaseManager.ISSZMBALV3_t_bd_item_info_item_subno) + "','" +
                    values.get(DatabaseManager.ISSZMBALV3_t_bd_item_info_item_name) + "','" +
                    values.get(DatabaseManager.ISSZMBALV3_t_bd_item_info_item_subname) + "','" +
                    values.get(DatabaseManager.ISSZMBALV3_t_bd_item_info_item_clsno) + "','" +
                    values.get(DatabaseManager.ISSZMBALV3_t_bd_item_info_item_brand) + "','" +
                    values.get(DatabaseManager.ISSZMBALV3_t_bd_item_info_item_brandname) + "'," +
                    values.get(DatabaseManager.ISSZMBALV3_t_bd_item_price) + "," +
                    values.get(DatabaseManager.ISSZMBALV3_t_bd_item_base_price) + "," +
                    values.get(DatabaseManager.ISSZMBALV3_t_bd_item_sale_price) + ",'" +
                    values.get(DatabaseManager.ISSZMBALV3_t_bd_item_combine_sta) + "','" +
                    values.get(DatabaseManager.ISSZMBALV3_t_bd_item_status) + "','" +
                    values.get(DatabaseManager.ISSZMBALV3_t_bd_item_display_flag) + "'," +
                    values.get(DatabaseManager.ISSZMBALV3_t_bd_item_po_cycle) + "," +
                    values.get(DatabaseManager.ISSZMBALV3_t_bd_item_so_cycle) + ",'" +
                    values.get(DatabaseManager.ISSZMBALV3_t_bd_item_automin_flag) + "','" +
                    values.get(DatabaseManager.ISSZMBALV3_t_bd_item_en_dis) + "','" +
                    values.get(DatabaseManager.ISSZMBALV3_t_bd_item_change_price) + "'," +
                    values.get(DatabaseManager.ISSZMBALV3_t_bd_item_purchase_tax) + "," +
                    values.get(DatabaseManager.ISSZMBALV3_t_bd_item_sale_tax) + "," +
                    values.get(DatabaseManager.ISSZMBALV3_t_bd_item_purchase_spec) + "," +
                    values.get(DatabaseManager.ISSZMBALV3_t_bd_item_shipment_spec) + ",'" +
                    values.get(DatabaseManager.ISSZMBALV3_t_bd_item_main_supcust) + "','" +
                    values.get(DatabaseManager.ISSZMBALV3_t_bd_item_item_stock) + "','" +
                    values.get(DatabaseManager.ISSZMBALV3_t_bd_item_build_date) + "','" +
                    values.get(DatabaseManager.ISSZMBALV3_t_bd_item_abc) + "','" +
                    values.get(DatabaseManager.ISSZMBALV3_t_bd_item_branch_price) + "','" +
                    values.get(DatabaseManager.ISSZMBALV3_t_bd_item_item_rem) + "'," +
                    values.get(DatabaseManager.ISSZMBALV3_t_bd_item_vip_price) + "," +
                    values.get(DatabaseManager.ISSZMBALV3_t_bd_item_sale_min_price) + ",'" +
                    values.get(DatabaseManager.ISSZMBALV3_t_bd_item_com_flag) + "','" +
                    values.get(DatabaseManager.ISSZMBALV3_t_bd_item_vip_acc_flag) + "'," +
                    values.get(DatabaseManager.ISSZMBALV3_t_bd_item_vip_acc_num) + ",'" +
                    values.get(DatabaseManager.ISSZMBALV3_t_bd_item_dpfm_type) + "'," +
                    values.get(DatabaseManager.ISSZMBALV3_t_bd_item_return_rate) + ",'" +
                    values.get(DatabaseManager.ISSZMBALV3_t_bd_item_sale_flag) + "'," +
                    values.get(DatabaseManager.ISSZMBALV3_t_bd_item_scheme_price) + "," +
                    values.get(DatabaseManager.ISSZMBALV3_t_bd_item_vip_price2) + "," +
                    values.get(DatabaseManager.ISSZMBALV3_t_bd_item_vip_price3) + "," +
                    values.get(DatabaseManager.ISSZMBALV3_t_bd_item_vip_price4) + "," +
                    values.get(DatabaseManager.ISSZMBALV3_t_bd_item_vip_price5) + "," +
                    values.get(DatabaseManager.ISSZMBALV3_t_bd_item_valid_days) + ",'" +
                    values.get(DatabaseManager.ISSZMBALV3_t_bd_item_item_com) + "','" +
                    values.get(DatabaseManager.ISSZMBALV3_t_bd_item_measure_flag) + "','" +
                    values.get(DatabaseManager.ISSZMBALV3_t_bd_item_item_sup_flag) + "'," +
                    values.get(DatabaseManager.ISSZMBALV3_t_bd_item_sup_rate) + ",'" +
                    values.get(DatabaseManager.ISSZMBALV3_t_bd_item_new_oper_id) + "','" +
                    values.get(DatabaseManager.ISSZMBALV3_t_bd_item_modify_oper_id) + "','" +
                    values.get(DatabaseManager.ISSZMBALV3_t_bd_item_en_gift) + "','" +
                    values.get(DatabaseManager.ISSZMBALV3_t_bd_item_promote_flag) + "','" +
                    values.get(DatabaseManager.ISSZMBALV3_t_bd_item_allow_flag) + "','" +
                    values.get(DatabaseManager.ISSZMBALV3_t_bd_item_hand_flag) + "')";
        } else {
            sql = "insert into " + DatabaseManager.ISSZMBALV3_t_bd_item_info_TABLE_NAME + "(" +
                    DatabaseManager.ISSZMBALV3_t_bd_item_info_item_no + "," +
                    DatabaseManager.ISSZMBALV3_t_bd_item_info_item_subno + "," +
                    DatabaseManager.ISSZMBALV3_t_bd_item_info_item_name + "," +
                    DatabaseManager.ISSZMBALV3_t_bd_item_info_item_subname + "," +
                    DatabaseManager.ISSZMBALV3_t_bd_item_info_item_clsno + "," +
                    DatabaseManager.ISSZMBALV3_t_bd_item_info_item_brand + "," +
                    DatabaseManager.ISSZMBALV3_t_bd_item_info_item_brandname + "," +
                    DatabaseManager.ISSZMBALV3_t_bd_item_price + "," +
                    DatabaseManager.ISSZMBALV3_t_bd_item_base_price + "," +
                    DatabaseManager.ISSZMBALV3_t_bd_item_sale_price + "," +
                    DatabaseManager.ISSZMBALV3_t_bd_item_combine_sta + "," +
                    DatabaseManager.ISSZMBALV3_t_bd_item_status + "," +
                    DatabaseManager.ISSZMBALV3_t_bd_item_display_flag + "," +
                    DatabaseManager.ISSZMBALV3_t_bd_item_po_cycle + "," +
                    DatabaseManager.ISSZMBALV3_t_bd_item_so_cycle + "," +
                    DatabaseManager.ISSZMBALV3_t_bd_item_automin_flag + "," +
                    DatabaseManager.ISSZMBALV3_t_bd_item_en_dis + "," +
                    DatabaseManager.ISSZMBALV3_t_bd_item_change_price + "," +
                    DatabaseManager.ISSZMBALV3_t_bd_item_purchase_tax + "," +
                    DatabaseManager.ISSZMBALV3_t_bd_item_sale_tax + "," +
                    DatabaseManager.ISSZMBALV3_t_bd_item_purchase_spec + "," +
                    DatabaseManager.ISSZMBALV3_t_bd_item_shipment_spec + "," +
                    DatabaseManager.ISSZMBALV3_t_bd_item_main_supcust + "," +
                    DatabaseManager.ISSZMBALV3_t_bd_item_item_stock + "," +
                    DatabaseManager.ISSZMBALV3_t_bd_item_build_date + "," +
                    DatabaseManager.ISSZMBALV3_t_bd_item_abc + "," +
                    DatabaseManager.ISSZMBALV3_t_bd_item_branch_price + "," +
                    DatabaseManager.ISSZMBALV3_t_bd_item_item_rem + "," +
                    DatabaseManager.ISSZMBALV3_t_bd_item_vip_price + "," +
                    DatabaseManager.ISSZMBALV3_t_bd_item_sale_min_price + "," +
                    DatabaseManager.ISSZMBALV3_t_bd_item_com_flag + "," +
                    DatabaseManager.ISSZMBALV3_t_bd_item_vip_acc_flag + "," +
                    DatabaseManager.ISSZMBALV3_t_bd_item_vip_acc_num + "," +
                    DatabaseManager.ISSZMBALV3_t_bd_item_dpfm_type + "," +
                    DatabaseManager.ISSZMBALV3_t_bd_item_return_rate + "," +
                    DatabaseManager.ISSZMBALV3_t_bd_item_sale_flag + "," +
                    DatabaseManager.ISSZMBALV3_t_bd_item_scheme_price + "," +
                    DatabaseManager.ISSZMBALV3_t_bd_item_vip_price2 + "," +
                    DatabaseManager.ISSZMBALV3_t_bd_item_vip_price3 + "," +
                    DatabaseManager.ISSZMBALV3_t_bd_item_vip_price4 + "," +
                    DatabaseManager.ISSZMBALV3_t_bd_item_vip_price5 + "," +
                    DatabaseManager.ISSZMBALV3_t_bd_item_valid_days + "," +
                    DatabaseManager.ISSZMBALV3_t_bd_item_item_com + "," +
                    DatabaseManager.ISSZMBALV3_t_bd_item_measure_flag + "," +
                    DatabaseManager.ISSZMBALV3_t_bd_item_item_sup_flag + "," +
                    DatabaseManager.ISSZMBALV3_t_bd_item_sup_rate + "," +
                    DatabaseManager.ISSZMBALV3_t_bd_item_en_gift + "," +
                    DatabaseManager.ISSZMBALV3_t_bd_item_promote_flag + "," +
                    DatabaseManager.ISSZMBALV3_t_bd_item_allow_flag + "," +
                    DatabaseManager.ISSZMBALV3_t_bd_item_hand_flag + ") values" + "('" +
                    values.get(DatabaseManager.ISSZMBALV3_t_bd_item_info_item_no) + "','" +
                    values.get(DatabaseManager.ISSZMBALV3_t_bd_item_info_item_subno) + "','" +
                    values.get(DatabaseManager.ISSZMBALV3_t_bd_item_info_item_name) + "','" +
                    values.get(DatabaseManager.ISSZMBALV3_t_bd_item_info_item_subname) + "','" +
                    values.get(DatabaseManager.ISSZMBALV3_t_bd_item_info_item_clsno) + "','" +
                    values.get(DatabaseManager.ISSZMBALV3_t_bd_item_info_item_brand) + "','" +
                    values.get(DatabaseManager.ISSZMBALV3_t_bd_item_info_item_brandname) + "'," +
                    values.get(DatabaseManager.ISSZMBALV3_t_bd_item_price) + "," +
                    values.get(DatabaseManager.ISSZMBALV3_t_bd_item_base_price) + "," +
                    values.get(DatabaseManager.ISSZMBALV3_t_bd_item_sale_price) + ",'" +
                    values.get(DatabaseManager.ISSZMBALV3_t_bd_item_combine_sta) + "','" +
                    values.get(DatabaseManager.ISSZMBALV3_t_bd_item_status) + "','" +
                    values.get(DatabaseManager.ISSZMBALV3_t_bd_item_display_flag) + "'," +
                    values.get(DatabaseManager.ISSZMBALV3_t_bd_item_po_cycle) + "," +
                    values.get(DatabaseManager.ISSZMBALV3_t_bd_item_so_cycle) + ",'" +
                    values.get(DatabaseManager.ISSZMBALV3_t_bd_item_automin_flag) + "','" +
                    values.get(DatabaseManager.ISSZMBALV3_t_bd_item_en_dis) + "','" +
                    values.get(DatabaseManager.ISSZMBALV3_t_bd_item_change_price) + "'," +
                    values.get(DatabaseManager.ISSZMBALV3_t_bd_item_purchase_tax) + "," +
                    values.get(DatabaseManager.ISSZMBALV3_t_bd_item_sale_tax) + "," +
                    values.get(DatabaseManager.ISSZMBALV3_t_bd_item_purchase_spec) + "," +
                    values.get(DatabaseManager.ISSZMBALV3_t_bd_item_shipment_spec) + ",'" +
                    values.get(DatabaseManager.ISSZMBALV3_t_bd_item_main_supcust) + "','" +
                    values.get(DatabaseManager.ISSZMBALV3_t_bd_item_item_stock) + "','" +
                    values.get(DatabaseManager.ISSZMBALV3_t_bd_item_build_date) + "','" +
                    values.get(DatabaseManager.ISSZMBALV3_t_bd_item_abc) + "','" +
                    values.get(DatabaseManager.ISSZMBALV3_t_bd_item_branch_price) + "','" +
                    values.get(DatabaseManager.ISSZMBALV3_t_bd_item_item_rem) + "'," +
                    values.get(DatabaseManager.ISSZMBALV3_t_bd_item_vip_price) + "," +
                    values.get(DatabaseManager.ISSZMBALV3_t_bd_item_sale_min_price) + ",'" +
                    values.get(DatabaseManager.ISSZMBALV3_t_bd_item_com_flag) + "','" +
                    values.get(DatabaseManager.ISSZMBALV3_t_bd_item_vip_acc_flag) + "'," +
                    values.get(DatabaseManager.ISSZMBALV3_t_bd_item_vip_acc_num) + ",'" +
                    values.get(DatabaseManager.ISSZMBALV3_t_bd_item_dpfm_type) + "'," +
                    values.get(DatabaseManager.ISSZMBALV3_t_bd_item_return_rate) + ",'" +
                    values.get(DatabaseManager.ISSZMBALV3_t_bd_item_sale_flag) + "'," +
                    values.get(DatabaseManager.ISSZMBALV3_t_bd_item_scheme_price) + "," +
                    values.get(DatabaseManager.ISSZMBALV3_t_bd_item_vip_price2) + "," +
                    values.get(DatabaseManager.ISSZMBALV3_t_bd_item_vip_price3) + "," +
                    values.get(DatabaseManager.ISSZMBALV3_t_bd_item_vip_price4) + "," +
                    values.get(DatabaseManager.ISSZMBALV3_t_bd_item_vip_price5) + "," +
                    values.get(DatabaseManager.ISSZMBALV3_t_bd_item_valid_days) + ",'" +
                    values.get(DatabaseManager.ISSZMBALV3_t_bd_item_item_com) + "','" +
                    values.get(DatabaseManager.ISSZMBALV3_t_bd_item_measure_flag) + "','" +
                    values.get(DatabaseManager.ISSZMBALV3_t_bd_item_item_sup_flag) + "'," +
                    values.get(DatabaseManager.ISSZMBALV3_t_bd_item_sup_rate) + ",'" +
                    values.get(DatabaseManager.ISSZMBALV3_t_bd_item_en_gift) + "','" +
                    values.get(DatabaseManager.ISSZMBALV3_t_bd_item_promote_flag) + "','" +
                    values.get(DatabaseManager.ISSZMBALV3_t_bd_item_allow_flag) + "','" +
                    values.get(DatabaseManager.ISSZMBALV3_t_bd_item_hand_flag) + "')";
        }
        stmt.executeUpdate(sql);//ResultSet类似Cursor
        row = stmt.getUpdateCount();
        stmt.close();
        if (con != null)
            con.close();
        return row;
    }

    public long replaceIsszmbalv3StockInfo(ContentValues values) throws Exception {
        long row = -1;
        String stockNo = (String) values.get(DatabaseManager.ISSZMBALV3_t_bd_item_info_item_no);
        // 加载驱动程序
        Class.forName("net.sourceforge.jtds.jdbc.Driver");
        Connection con = DriverManager.getConnection("jdbc:jtds:sqlserver://" + BarApplication.getInstance().mSqlServerHost + "/" + BarApplication.getInstance().mSqlServerDatabaseName, BarApplication.getInstance().mSqlServerUser, BarApplication.getInstance().mSqlServerPassword);
        String sql = "update " + DatabaseManager.ISSZMBALV3_t_bd_item_info_TABLE_NAME + " set " +
                DatabaseManager.ISSZMBALV3_t_bd_item_info_item_no + "='" + values.get(DatabaseManager.ISSZMBALV3_t_bd_item_info_item_no) + "'," +
                DatabaseManager.ISSZMBALV3_t_bd_item_info_item_name + "='" + values.get(DatabaseManager.ISSZMBALV3_t_bd_item_info_item_name) + "'," +
                DatabaseManager.ISSZMBALV3_t_bd_item_info_item_subname + "='" + values.get(DatabaseManager.ISSZMBALV3_t_bd_item_info_item_subname) + "'," +
                DatabaseManager.ISSZMBALV3_t_bd_item_info_item_clsno + "='" + values.get(DatabaseManager.ISSZMBALV3_t_bd_item_info_item_clsno) + "'," +
                DatabaseManager.ISSZMBALV3_t_bd_item_price + "=" + values.get(DatabaseManager.ISSZMBALV3_t_bd_item_price) + "," +
                DatabaseManager.ISSZMBALV3_t_bd_item_sale_price + "=" + values.get(DatabaseManager.ISSZMBALV3_t_bd_item_sale_price) + "," +
                DatabaseManager.ISSZMBALV3_t_bd_item_item_rem + "='" + values.get(DatabaseManager.ISSZMBALV3_t_bd_item_item_rem) + "'," +
                DatabaseManager.ISSZMBALV3_t_bd_item_vip_acc_flag + "='" + values.get(DatabaseManager.ISSZMBALV3_t_bd_item_vip_acc_flag) + "'" +
                " WHERE " + DatabaseManager.ISSZMBALV3_t_bd_item_info_item_no + "='" + stockNo + "'";
        Statement stmt = con.createStatement();//创建Statement
        stmt.executeUpdate(sql);//ResultSet类似Cursor
        row = stmt.getUpdateCount();
        stmt.close();
        if (con != null)
            con.close();
        return row;
    }

    public long deleteIsszmbalv3StockInfo(String stockNo) throws Exception {
        long row = -1;
        // 加载驱动程序
        Class.forName("net.sourceforge.jtds.jdbc.Driver");
        Connection con = DriverManager.getConnection("jdbc:jtds:sqlserver://" + BarApplication.getInstance().mSqlServerHost + "/" + BarApplication.getInstance().mSqlServerDatabaseName, BarApplication.getInstance().mSqlServerUser, BarApplication.getInstance().mSqlServerPassword);
        String sql = "DELETE FROM " + DatabaseManager.ISSZMBALV3_t_bd_item_info_TABLE_NAME + " WHERE " + DatabaseManager.ISSZMBALV3_t_bd_item_info_item_no + " = '" + stockNo + "'";
        Statement stmt = con.createStatement();//创建Statement
        stmt.executeUpdate(sql);//ResultSet类似Cursor
        row = stmt.getUpdateCount();
        stmt.close();
        if (con != null)
            con.close();
        return row;
    }

    public int getIsszmbalv3MemberCount(String memberPointStr) throws Exception {
        // 加载驱动程序
        Class.forName("net.sourceforge.jtds.jdbc.Driver");
        Connection con = DriverManager.getConnection("jdbc:jtds:sqlserver://" + BarApplication.getInstance().mSqlServerHost + "/" + BarApplication.getInstance().mSqlServerDatabaseName, BarApplication.getInstance().mSqlServerUser, BarApplication.getInstance().mSqlServerPassword);
        String sql = "";
        if (!memberPointStr.equals("-1"))
            sql = "select count(1) as totalCount from " + DatabaseManager.ISSZMBALV3_t_rm_vip_info_TABLE_NAME + " WHERE " + DatabaseManager.ISSZMBALV3_t_rm_vip_acc_num + " >= " + memberPointStr;
        else
            sql = "select count(1) as totalCount from " + DatabaseManager.ISSZMBALV3_t_rm_vip_info_TABLE_NAME;
        int totalCount = -1;
        try {
            Statement stmt = con.createStatement();//创建Statement
            ResultSet rs = stmt.executeQuery(sql);//ResultSet类似Cursor
            if (rs.next()) {
                totalCount = rs.getInt(1);
            }
            rs.close();
            stmt.close();
        } catch (Exception e) {
            e.printStackTrace();
        }

        if (con != null)
            con.close();
        return totalCount;
    }

    private List<Isszmbalv3Member> collectIsszmbalv3MemberInfoList(ResultSet rs) throws Exception {
        List<Isszmbalv3Member> result = new ArrayList<Isszmbalv3Member>();
        if (rs != null) {
            while (rs.next()) {
                Isszmbalv3Member isszmbalv3MemberInfo = new Isszmbalv3Member();
                isszmbalv3MemberInfo.memberCardType = rs.getString(DatabaseManager.ISSZMBALV3_t_rm_vip_card_type);
                isszmbalv3MemberInfo.memberCardId = rs.getString(DatabaseManager.ISSZMBALV3_t_rm_vip_card_id);
                isszmbalv3MemberInfo.memberCardNo = rs.getString(DatabaseManager.ISSZMBALV3_t_rm_vip_card_no);
                isszmbalv3MemberInfo.memberName = rs.getString(DatabaseManager.ISSZMBALV3_t_rm_vip_vip_name);
                isszmbalv3MemberInfo.memberSex = rs.getString(DatabaseManager.ISSZMBALV3_t_rm_vip_vip_sex);
                isszmbalv3MemberInfo.memberTel = rs.getString(DatabaseManager.ISSZMBALV3_t_rm_vip_vip_tel);
                isszmbalv3MemberInfo.memberEmail = rs.getString(DatabaseManager.ISSZMBALV3_t_rm_vip_vip_email);
                isszmbalv3MemberInfo.memberMobile = rs.getString(DatabaseManager.ISSZMBALV3_t_rm_vip_mobile);
                isszmbalv3MemberInfo.memberConsumCount = rs.getInt(DatabaseManager.ISSZMBALV3_t_rm_vip_consum_count);
                isszmbalv3MemberInfo.memberConsumAmt = rs.getFloat(DatabaseManager.ISSZMBALV3_t_rm_vip_consum_amt);
                isszmbalv3MemberInfo.memberAccNum = rs.getFloat(DatabaseManager.ISSZMBALV3_t_rm_vip_acc_num);
                isszmbalv3MemberInfo.memberDecNum = rs.getFloat(DatabaseManager.ISSZMBALV3_t_rm_vip_dec_num);
                isszmbalv3MemberInfo.memberVipStartDay = rs.getDate(DatabaseManager.ISSZMBALV3_t_rm_vip_vip_start_date);
                isszmbalv3MemberInfo.memberVipEndDay = rs.getDate(DatabaseManager.ISSZMBALV3_t_rm_vip_vip_end_date);
                isszmbalv3MemberInfo.memberSavStartDay = rs.getDate(DatabaseManager.ISSZMBALV3_t_rm_vip_sav_start_date);
                isszmbalv3MemberInfo.memberSavEndDay = rs.getDate(DatabaseManager.ISSZMBALV3_t_rm_vip_sav_end_date);

                String str = "#";
                if (!TextUtils.isEmpty(isszmbalv3MemberInfo.memberName))
                    str = Utils.cn2FirstSpell(isszmbalv3MemberInfo.memberName).substring(0, 1);
                if (str.matches("[A-Z]")) {
                    isszmbalv3MemberInfo.setSortLetters(str);
                } else {
                    isszmbalv3MemberInfo.setSortLetters("#");
                }

                result.add(isszmbalv3MemberInfo);
            }
            rs.close();
        }
        return result;
    }

    public List<Isszmbalv3Member> getAllIsszmbalv3Member(int startIndex, int pageSize, int maxCount, String memberPointStr, int sortMode) throws Exception {
        // 加载驱动程序
        Class.forName("net.sourceforge.jtds.jdbc.Driver");
        Connection con = DriverManager.getConnection("jdbc:jtds:sqlserver://" + BarApplication.getInstance().mSqlServerHost + "/" + BarApplication.getInstance().mSqlServerDatabaseName, BarApplication.getInstance().mSqlServerUser, BarApplication.getInstance().mSqlServerPassword);
        String sortName = DatabaseManager.ISSZMBALV3_t_rm_vip_vip_name;
        String sortUpOrDown0 = " ASC ";
        String sortUpOrDown1 = " DESC ";
        switch (sortMode) {
            case 0:
                sortName = DatabaseManager.ISSZMBALV3_t_rm_vip_vip_name;
                sortUpOrDown0 = " ASC ";
                sortUpOrDown1 = " DESC ";
                break;
            case 1:
                sortName = DatabaseManager.ISSZMBALV3_t_rm_vip_vip_name;
                sortUpOrDown0 = " DESC ";
                sortUpOrDown1 = " ASC ";
                break;
            case 2:
                sortName = DatabaseManager.ISSZMBALV3_t_rm_vip_acc_num;
                sortUpOrDown0 = " ASC ";
                sortUpOrDown1 = " DESC ";
                break;
            case 3:
                sortName = DatabaseManager.ISSZMBALV3_t_rm_vip_acc_num;
                sortUpOrDown0 = " DESC ";
                sortUpOrDown1 = " ASC ";
                break;
            case 4:
                sortName = DatabaseManager.ISSZMBALV3_t_rm_vip_consum_amt;
                sortUpOrDown0 = " ASC ";
                sortUpOrDown1 = " DESC ";
                break;
            case 5:
                sortName = DatabaseManager.ISSZMBALV3_t_rm_vip_consum_amt;
                sortUpOrDown0 = " DESC ";
                sortUpOrDown1 = " ASC ";
                break;
        }

        String sql = "";
        if (pageSize == -1) {
            sql = "SELECT " + DatabaseManager.ISSZMBALV3_t_rm_vip_card_type + "," +
                    DatabaseManager.ISSZMBALV3_t_rm_vip_card_id + "," +
                    DatabaseManager.ISSZMBALV3_t_rm_vip_card_no + "," +
                    DatabaseManager.ISSZMBALV3_t_rm_vip_vip_name + "," +
                    DatabaseManager.ISSZMBALV3_t_rm_vip_vip_sex + "," +
                    DatabaseManager.ISSZMBALV3_t_rm_vip_vip_tel + "," +
                    DatabaseManager.ISSZMBALV3_t_rm_vip_vip_email + "," +
                    DatabaseManager.ISSZMBALV3_t_rm_vip_mobile + "," +
                    DatabaseManager.ISSZMBALV3_t_rm_vip_consum_count + "," +
                    DatabaseManager.ISSZMBALV3_t_rm_vip_consum_amt + "," +
                    DatabaseManager.ISSZMBALV3_t_rm_vip_acc_num + "," +
                    DatabaseManager.ISSZMBALV3_t_rm_vip_dec_num + "," +
                    DatabaseManager.ISSZMBALV3_t_rm_vip_vip_start_date + "," +
                    DatabaseManager.ISSZMBALV3_t_rm_vip_vip_end_date + "," +
                    DatabaseManager.ISSZMBALV3_t_rm_vip_sav_start_date + "," +
                    DatabaseManager.ISSZMBALV3_t_rm_vip_sav_end_date +
                    " FROM " + DatabaseManager.ISSZMBALV3_t_rm_vip_info_TABLE_NAME + " ORDER BY " + sortName + sortUpOrDown0;
        } else {
            int endIndex = startIndex + pageSize;
            if (endIndex > maxCount) {
                pageSize = pageSize - (endIndex - maxCount);
            }
//            sql =  "SELECT top " + pageSize + " * FROM " +
//                    "( " +
//                    "SELECT top " + endIndex + " * FROM " + DatabaseManager.ISSZMBALV3_t_rm_vip_info_TABLE_NAME +
//                    " WHERE " + DatabaseManager.ISSZMBALV3_t_rm_vip_acc_num + " >= " + memberPointStr + " ORDER BY " + sortName + sortUpOrDown1 +
//                    ") w ORDER BY w." + sortName + sortUpOrDown0 ;

            sql = "SELECT * FROM " + DatabaseManager.ISSZMBALV3_t_rm_vip_info_TABLE_NAME + " w1 " +
                    " WHERE " + sortName + " IN ( " +
                    "SELECT top " + pageSize + " " + sortName + " FROM " +
                    "( " +
                    "SELECT top " + endIndex + " " + sortName + " FROM " + DatabaseManager.ISSZMBALV3_t_rm_vip_info_TABLE_NAME +
                    " WHERE " + DatabaseManager.ISSZMBALV3_t_rm_vip_acc_num + " >= " + memberPointStr + " ORDER BY " + sortName + sortUpOrDown0 +
                    ") w ORDER BY w." + sortName + sortUpOrDown1 +
                    ") " +
                    " ORDER BY w1." + sortName + sortUpOrDown0;
        }

        List<Isszmbalv3Member> result = null;
        try {
            Statement stmt = con.createStatement();//创建Statement
            ResultSet rs = stmt.executeQuery(sql);//ResultSet类似Cursor
            result = collectIsszmbalv3MemberInfoList(rs);
            stmt.close();
        } catch (Exception e) {
            e.printStackTrace();
        }

        if (con != null)
            con.close();
        return result;
    }

    public List<Isszmbalv3Member> getByIsszmbalv3MemberCardNo(String cardno, int sortMode) throws Exception {
        // 加载驱动程序
        Class.forName("net.sourceforge.jtds.jdbc.Driver");
        Connection con = DriverManager.getConnection("jdbc:jtds:sqlserver://" + BarApplication.getInstance().mSqlServerHost + "/" + BarApplication.getInstance().mSqlServerDatabaseName, BarApplication.getInstance().mSqlServerUser, BarApplication.getInstance().mSqlServerPassword);
        String sortName = DatabaseManager.ISSZMBALV3_t_rm_vip_vip_name;
        String sortUpOrDown0 = " ASC ";
        String sortUpOrDown1 = " DESC ";
        switch (sortMode) {
            case 0:
                sortName = DatabaseManager.ISSZMBALV3_t_rm_vip_vip_name;
                sortUpOrDown0 = " ASC ";
                sortUpOrDown1 = " DESC ";
                break;
            case 1:
                sortName = DatabaseManager.ISSZMBALV3_t_rm_vip_vip_name;
                sortUpOrDown0 = " DESC ";
                sortUpOrDown1 = " ASC ";
                break;
            case 2:
                sortName = DatabaseManager.ISSZMBALV3_t_rm_vip_acc_num;
                sortUpOrDown0 = " ASC ";
                sortUpOrDown1 = " DESC ";
                break;
            case 3:
                sortName = DatabaseManager.ISSZMBALV3_t_rm_vip_acc_num;
                sortUpOrDown0 = " DESC ";
                sortUpOrDown1 = " ASC ";
                break;
            case 4:
                sortName = DatabaseManager.ISSZMBALV3_t_rm_vip_consum_amt;
                sortUpOrDown0 = " ASC ";
                sortUpOrDown1 = " DESC ";
                break;
            case 5:
                sortName = DatabaseManager.ISSZMBALV3_t_rm_vip_consum_amt;
                sortUpOrDown0 = " DESC ";
                sortUpOrDown1 = " ASC ";
                break;
        }
        String sql = "SELECT " + DatabaseManager.ISSZMBALV3_t_rm_vip_card_type + "," +
                DatabaseManager.ISSZMBALV3_t_rm_vip_card_id + "," +
                DatabaseManager.ISSZMBALV3_t_rm_vip_card_no + "," +
                DatabaseManager.ISSZMBALV3_t_rm_vip_vip_name + "," +
                DatabaseManager.ISSZMBALV3_t_rm_vip_vip_sex + "," +
                DatabaseManager.ISSZMBALV3_t_rm_vip_vip_tel + "," +
                DatabaseManager.ISSZMBALV3_t_rm_vip_vip_email + "," +
                DatabaseManager.ISSZMBALV3_t_rm_vip_mobile + "," +
                DatabaseManager.ISSZMBALV3_t_rm_vip_consum_count + "," +
                DatabaseManager.ISSZMBALV3_t_rm_vip_consum_amt + "," +
                DatabaseManager.ISSZMBALV3_t_rm_vip_acc_num + "," +
                DatabaseManager.ISSZMBALV3_t_rm_vip_dec_num + "," +
                DatabaseManager.ISSZMBALV3_t_rm_vip_vip_start_date + "," +
                DatabaseManager.ISSZMBALV3_t_rm_vip_vip_end_date + "," +
                DatabaseManager.ISSZMBALV3_t_rm_vip_sav_start_date + "," +
                DatabaseManager.ISSZMBALV3_t_rm_vip_sav_end_date +
                " FROM " + DatabaseManager.ISSZMBALV3_t_rm_vip_info_TABLE_NAME + " WHERE " + DatabaseManager.ISSZMBALV3_t_rm_vip_card_no + "  like '%" + cardno + "%'" + " ORDER BY " + sortName + sortUpOrDown0;
        Statement stmt = con.createStatement();//创建Statement
        ResultSet rs = stmt.executeQuery(sql);//ResultSet类似Cursor
        List<Isszmbalv3Member> result = collectIsszmbalv3MemberInfoList(rs);
        stmt.close();
        if (con != null)
            con.close();
        return result;
    }

    public List<Isszmbalv3Member> getByIsszmbalv3MemberName(String name, int sortMode) throws Exception {
        // 加载驱动程序
        Class.forName("net.sourceforge.jtds.jdbc.Driver");
        Connection con = DriverManager.getConnection("jdbc:jtds:sqlserver://" + BarApplication.getInstance().mSqlServerHost + "/" + BarApplication.getInstance().mSqlServerDatabaseName, BarApplication.getInstance().mSqlServerUser, BarApplication.getInstance().mSqlServerPassword);
        String sortName = DatabaseManager.ISSZMBALV3_t_rm_vip_vip_name;
        String sortUpOrDown0 = " ASC ";
        String sortUpOrDown1 = " DESC ";
        switch (sortMode) {
            case 0:
                sortName = DatabaseManager.ISSZMBALV3_t_rm_vip_vip_name;
                sortUpOrDown0 = " ASC ";
                sortUpOrDown1 = " DESC ";
                break;
            case 1:
                sortName = DatabaseManager.ISSZMBALV3_t_rm_vip_vip_name;
                sortUpOrDown0 = " DESC ";
                sortUpOrDown1 = " ASC ";
                break;
            case 2:
                sortName = DatabaseManager.ISSZMBALV3_t_rm_vip_acc_num;
                sortUpOrDown0 = " ASC ";
                sortUpOrDown1 = " DESC ";
                break;
            case 3:
                sortName = DatabaseManager.ISSZMBALV3_t_rm_vip_acc_num;
                sortUpOrDown0 = " DESC ";
                sortUpOrDown1 = " ASC ";
                break;
            case 4:
                sortName = DatabaseManager.ISSZMBALV3_t_rm_vip_consum_amt;
                sortUpOrDown0 = " ASC ";
                sortUpOrDown1 = " DESC ";
                break;
            case 5:
                sortName = DatabaseManager.ISSZMBALV3_t_rm_vip_consum_amt;
                sortUpOrDown0 = " DESC ";
                sortUpOrDown1 = " ASC ";
                break;
        }

        String sql = "SELECT " + DatabaseManager.ISSZMBALV3_t_rm_vip_card_type + "," +
                DatabaseManager.ISSZMBALV3_t_rm_vip_card_id + "," +
                DatabaseManager.ISSZMBALV3_t_rm_vip_card_no + "," +
                DatabaseManager.ISSZMBALV3_t_rm_vip_vip_name + "," +
                DatabaseManager.ISSZMBALV3_t_rm_vip_vip_sex + "," +
                DatabaseManager.ISSZMBALV3_t_rm_vip_vip_tel + "," +
                DatabaseManager.ISSZMBALV3_t_rm_vip_vip_email + "," +
                DatabaseManager.ISSZMBALV3_t_rm_vip_mobile + "," +
                DatabaseManager.ISSZMBALV3_t_rm_vip_consum_count + "," +
                DatabaseManager.ISSZMBALV3_t_rm_vip_consum_amt + "," +
                DatabaseManager.ISSZMBALV3_t_rm_vip_acc_num + "," +
                DatabaseManager.ISSZMBALV3_t_rm_vip_dec_num + "," +
                DatabaseManager.ISSZMBALV3_t_rm_vip_vip_start_date + "," +
                DatabaseManager.ISSZMBALV3_t_rm_vip_vip_end_date + "," +
                DatabaseManager.ISSZMBALV3_t_rm_vip_sav_start_date + "," +
                DatabaseManager.ISSZMBALV3_t_rm_vip_sav_end_date +
                " FROM " + DatabaseManager.ISSZMBALV3_t_rm_vip_info_TABLE_NAME + " WHERE " + DatabaseManager.ISSZMBALV3_t_rm_vip_vip_name + "  like '%" + name + "%'" + " ORDER BY " + sortName + sortUpOrDown0;
        Statement stmt = con.createStatement();//创建Statement
        ResultSet rs = stmt.executeQuery(sql);//ResultSet类似Cursor
        List<Isszmbalv3Member> result = collectIsszmbalv3MemberInfoList(rs);
        stmt.close();
        if (con != null)
            con.close();
        return result;
    }

    public List<Isszmbalv3Member> getByIsszmbalv3MemberMobile(String mobile, int sortMode) throws Exception {
        // 加载驱动程序
        Class.forName("net.sourceforge.jtds.jdbc.Driver");
        Connection con = DriverManager.getConnection("jdbc:jtds:sqlserver://" + BarApplication.getInstance().mSqlServerHost + "/" + BarApplication.getInstance().mSqlServerDatabaseName, BarApplication.getInstance().mSqlServerUser, BarApplication.getInstance().mSqlServerPassword);
        String sortName = DatabaseManager.ISSZMBALV3_t_rm_vip_vip_name;
        String sortUpOrDown0 = " ASC ";
        String sortUpOrDown1 = " DESC ";
        switch (sortMode) {
            case 0:
                sortName = DatabaseManager.ISSZMBALV3_t_rm_vip_vip_name;
                sortUpOrDown0 = " ASC ";
                sortUpOrDown1 = " DESC ";
                break;
            case 1:
                sortName = DatabaseManager.ISSZMBALV3_t_rm_vip_vip_name;
                sortUpOrDown0 = " DESC ";
                sortUpOrDown1 = " ASC ";
                break;
            case 2:
                sortName = DatabaseManager.ISSZMBALV3_t_rm_vip_acc_num;
                sortUpOrDown0 = " ASC ";
                sortUpOrDown1 = " DESC ";
                break;
            case 3:
                sortName = DatabaseManager.ISSZMBALV3_t_rm_vip_acc_num;
                sortUpOrDown0 = " DESC ";
                sortUpOrDown1 = " ASC ";
                break;
            case 4:
                sortName = DatabaseManager.ISSZMBALV3_t_rm_vip_consum_amt;
                sortUpOrDown0 = " ASC ";
                sortUpOrDown1 = " DESC ";
                break;
            case 5:
                sortName = DatabaseManager.ISSZMBALV3_t_rm_vip_consum_amt;
                sortUpOrDown0 = " DESC ";
                sortUpOrDown1 = " ASC ";
                break;
        }

        String sql = "SELECT " + DatabaseManager.ISSZMBALV3_t_rm_vip_card_type + "," +
                DatabaseManager.ISSZMBALV3_t_rm_vip_card_id + "," +
                DatabaseManager.ISSZMBALV3_t_rm_vip_card_no + "," +
                DatabaseManager.ISSZMBALV3_t_rm_vip_vip_name + "," +
                DatabaseManager.ISSZMBALV3_t_rm_vip_vip_sex + "," +
                DatabaseManager.ISSZMBALV3_t_rm_vip_vip_tel + "," +
                DatabaseManager.ISSZMBALV3_t_rm_vip_vip_email + "," +
                DatabaseManager.ISSZMBALV3_t_rm_vip_mobile + "," +
                DatabaseManager.ISSZMBALV3_t_rm_vip_consum_count + "," +
                DatabaseManager.ISSZMBALV3_t_rm_vip_consum_amt + "," +
                DatabaseManager.ISSZMBALV3_t_rm_vip_acc_num + "," +
                DatabaseManager.ISSZMBALV3_t_rm_vip_dec_num + "," +
                DatabaseManager.ISSZMBALV3_t_rm_vip_vip_start_date + "," +
                DatabaseManager.ISSZMBALV3_t_rm_vip_vip_end_date + "," +
                DatabaseManager.ISSZMBALV3_t_rm_vip_sav_start_date + "," +
                DatabaseManager.ISSZMBALV3_t_rm_vip_sav_end_date +
                " FROM " + DatabaseManager.ISSZMBALV3_t_rm_vip_info_TABLE_NAME + " WHERE " + DatabaseManager.ISSZMBALV3_t_rm_vip_mobile + "  like '%" + mobile + "%' or " + DatabaseManager.ISSZMBALV3_t_rm_vip_vip_tel + "  like '%" + mobile + "%'" + " ORDER BY " + sortName + sortUpOrDown0;
        Statement stmt = con.createStatement();//创建Statement
        ResultSet rs = stmt.executeQuery(sql);//ResultSet类似Cursor
        List<Isszmbalv3Member> result = collectIsszmbalv3MemberInfoList(rs);
        stmt.close();
        if (con != null)
            con.close();
        return result;
    }

    public long deleteIsszmbalv3Member(String memberCardId) throws Exception {
        long row = -1;
        // 加载驱动程序
        Class.forName("net.sourceforge.jtds.jdbc.Driver");
        Connection con = DriverManager.getConnection("jdbc:jtds:sqlserver://" + BarApplication.getInstance().mSqlServerHost + "/" + BarApplication.getInstance().mSqlServerDatabaseName, BarApplication.getInstance().mSqlServerUser, BarApplication.getInstance().mSqlServerPassword);
        String sql = "delete from " + DatabaseManager.ISSZMBALV3_t_rm_vip_info_TABLE_NAME + " WHERE " + DatabaseManager.ISSZMBALV3_t_rm_vip_card_id + "=" + memberCardId;
        Statement stmt = con.createStatement();//创建Statement
        stmt.executeUpdate(sql);//ResultSet类似Cursor
        row = stmt.getUpdateCount();
        stmt.close();
        if (con != null)
            con.close();
        return row;
    }

    public long insertIsszmbalv3Member(ContentValues values) throws Exception {
        long row = -1;
        // 加载驱动程序
        Class.forName("net.sourceforge.jtds.jdbc.Driver");
        Connection con = DriverManager.getConnection("jdbc:jtds:sqlserver://" + BarApplication.getInstance().mSqlServerHost + "/" + BarApplication.getInstance().mSqlServerDatabaseName, BarApplication.getInstance().mSqlServerUser, BarApplication.getInstance().mSqlServerPassword);
        String sql = "insert into " + DatabaseManager.ISSZMBALV3_t_rm_vip_info_TABLE_NAME + "(" +
                DatabaseManager.ISSZMBALV3_t_rm_vip_card_id + "," +
                DatabaseManager.ISSZMBALV3_t_rm_vip_card_no + "," +
                DatabaseManager.ISSZMBALV3_t_rm_vip_card_type + "," +
                DatabaseManager.ISSZMBALV3_t_rm_vip_vip_name + "," +
                DatabaseManager.ISSZMBALV3_t_rm_vip_vip_rem + "," +
                DatabaseManager.ISSZMBALV3_t_rm_vip_vip_sex + "," +
                DatabaseManager.ISSZMBALV3_t_rm_vip_vip_tel + "," +
                DatabaseManager.ISSZMBALV3_t_rm_vip_vip_email + "," +
                DatabaseManager.ISSZMBALV3_t_rm_vip_mobile + "," +
                DatabaseManager.ISSZMBALV3_t_rm_vip_vip_start_date + "," +
                DatabaseManager.ISSZMBALV3_t_rm_vip_vip_end_date + "," +
                DatabaseManager.ISSZMBALV3_t_rm_vip_sav_start_date + "," +
                DatabaseManager.ISSZMBALV3_t_rm_vip_sav_end_date + "," +

                DatabaseManager.ISSZMBALV3_t_rm_vip_oper_id + "," +
                DatabaseManager.ISSZMBALV3_t_rm_vip_oper_date + "," +
                DatabaseManager.ISSZMBALV3_t_rm_vip_send_amt + "," +
                DatabaseManager.ISSZMBALV3_t_rm_vip_vip_tax + "," +
                DatabaseManager.ISSZMBALV3_t_rm_vip_card_status + "," +
                DatabaseManager.ISSZMBALV3_t_rm_vip_use_num + "," +
                DatabaseManager.ISSZMBALV3_t_rm_vip_save_amt + "," +
                DatabaseManager.ISSZMBALV3_t_rm_vip_residual_amt + "," +
                DatabaseManager.ISSZMBALV3_t_rm_vip_consum_amt + "," +
                DatabaseManager.ISSZMBALV3_t_rm_vip_consum_count + "," +
                DatabaseManager.ISSZMBALV3_t_rm_vip_now_acc_num + "," +
                DatabaseManager.ISSZMBALV3_t_rm_vip_acc_num + "," +
                DatabaseManager.ISSZMBALV3_t_rm_vip_dec_num + "," +

                DatabaseManager.ISSZMBALV3_t_rm_vip_marriage + "," +
                DatabaseManager.ISSZMBALV3_t_rm_vip_nation + "," +
                DatabaseManager.ISSZMBALV3_t_rm_vip_modify_date + "," +
                DatabaseManager.ISSZMBALV3_t_rm_vip_item_amt + "," +
                DatabaseManager.ISSZMBALV3_t_rm_vip_branch_no + "," +
                DatabaseManager.ISSZMBALV3_t_rm_vip_bth_flag + "," +
                DatabaseManager.ISSZMBALV3_t_rm_vip_oper_date_gi + "," +
                DatabaseManager.ISSZMBALV3_t_rm_vip_oper_id_gi + ") values" + "('" +
                values.get(DatabaseManager.ISSZMBALV3_t_rm_vip_card_id) + "','" +
                values.get(DatabaseManager.ISSZMBALV3_t_rm_vip_card_no) + "','" +
                values.get(DatabaseManager.ISSZMBALV3_t_rm_vip_card_type) + "','" +
                values.get(DatabaseManager.ISSZMBALV3_t_rm_vip_vip_name) + "','" +
                values.get(DatabaseManager.ISSZMBALV3_t_rm_vip_vip_rem) + "','" +
                values.get(DatabaseManager.ISSZMBALV3_t_rm_vip_vip_sex) + "','" +
                values.get(DatabaseManager.ISSZMBALV3_t_rm_vip_vip_tel) + "','" +
                values.get(DatabaseManager.ISSZMBALV3_t_rm_vip_vip_email) + "','" +
                values.get(DatabaseManager.ISSZMBALV3_t_rm_vip_mobile) + "','" +
                values.get(DatabaseManager.ISSZMBALV3_t_rm_vip_vip_start_date) + "','" +
                values.get(DatabaseManager.ISSZMBALV3_t_rm_vip_vip_end_date) + "','" +
                values.get(DatabaseManager.ISSZMBALV3_t_rm_vip_sav_start_date) + "','" +
                values.get(DatabaseManager.ISSZMBALV3_t_rm_vip_sav_end_date) + "','" +

                values.get(DatabaseManager.ISSZMBALV3_t_rm_vip_oper_id) + "','" +
                values.get(DatabaseManager.ISSZMBALV3_t_rm_vip_oper_date) + "'," +
                values.get(DatabaseManager.ISSZMBALV3_t_rm_vip_send_amt) + "," +
                values.get(DatabaseManager.ISSZMBALV3_t_rm_vip_vip_tax) + ",'" +
                values.get(DatabaseManager.ISSZMBALV3_t_rm_vip_card_status) + "'," +
                values.get(DatabaseManager.ISSZMBALV3_t_rm_vip_use_num) + "," +
                values.get(DatabaseManager.ISSZMBALV3_t_rm_vip_save_amt) + ",'" +
                values.get(DatabaseManager.ISSZMBALV3_t_rm_vip_residual_amt) + "'," +
                values.get(DatabaseManager.ISSZMBALV3_t_rm_vip_consum_amt) + "," +
                values.get(DatabaseManager.ISSZMBALV3_t_rm_vip_consum_count) + "," +
                values.get(DatabaseManager.ISSZMBALV3_t_rm_vip_now_acc_num) + "," +
                values.get(DatabaseManager.ISSZMBALV3_t_rm_vip_acc_num) + "," +
                values.get(DatabaseManager.ISSZMBALV3_t_rm_vip_dec_num) + ",'" +

                values.get(DatabaseManager.ISSZMBALV3_t_rm_vip_marriage) + "','" +
                values.get(DatabaseManager.ISSZMBALV3_t_rm_vip_nation) + "','" +
                values.get(DatabaseManager.ISSZMBALV3_t_rm_vip_modify_date) + "'," +
                values.get(DatabaseManager.ISSZMBALV3_t_rm_vip_item_amt) + ",'" +
                values.get(DatabaseManager.ISSZMBALV3_t_rm_vip_branch_no) + "','" +
                values.get(DatabaseManager.ISSZMBALV3_t_rm_vip_bth_flag) + "','" +
                values.get(DatabaseManager.ISSZMBALV3_t_rm_vip_oper_date_gi) + "','" +
                values.get(DatabaseManager.ISSZMBALV3_t_rm_vip_oper_id_gi) + "')";
        Statement stmt = con.createStatement();//创建Statement
        stmt.executeUpdate(sql);//ResultSet类似Cursor
        row = stmt.getUpdateCount();
        stmt.close();
        if (con != null)
            con.close();
        return row;
    }

    public long replaceIsszmbalv3Member(ContentValues values) throws Exception {
        long row = -1;
        String memberCardId = (String) values.get(DatabaseManager.ISSZMBALV3_t_rm_vip_card_id);
        // 加载驱动程序
        Class.forName("net.sourceforge.jtds.jdbc.Driver");
        Connection con = DriverManager.getConnection("jdbc:jtds:sqlserver://" + BarApplication.getInstance().mSqlServerHost + "/" + BarApplication.getInstance().mSqlServerDatabaseName, BarApplication.getInstance().mSqlServerUser, BarApplication.getInstance().mSqlServerPassword);
        String sql = "update " + DatabaseManager.ISSZMBALV3_t_rm_vip_info_TABLE_NAME + " set " +
                DatabaseManager.ISSZMBALV3_t_rm_vip_card_no + "='" + values.get(DatabaseManager.ISSZMBALV3_t_rm_vip_card_no) + "'," +
                DatabaseManager.ISSZMBALV3_t_rm_vip_card_type + "='" + values.get(DatabaseManager.ISSZMBALV3_t_rm_vip_card_type) + "'," +
                DatabaseManager.ISSZMBALV3_t_rm_vip_vip_name + "='" + values.get(DatabaseManager.ISSZMBALV3_t_rm_vip_vip_name) + "'," +
                DatabaseManager.ISSZMBALV3_t_rm_vip_vip_rem + "='" + values.get(DatabaseManager.ISSZMBALV3_t_rm_vip_vip_rem) + "'," +
                DatabaseManager.ISSZMBALV3_t_rm_vip_vip_sex + "='" + values.get(DatabaseManager.ISSZMBALV3_t_rm_vip_vip_sex) + "'," +
                DatabaseManager.ISSZMBALV3_t_rm_vip_mobile + "='" + values.get(DatabaseManager.ISSZMBALV3_t_rm_vip_mobile) + "'," +
                DatabaseManager.ISSZMBALV3_t_rm_vip_vip_tel + "='" + values.get(DatabaseManager.ISSZMBALV3_t_rm_vip_vip_tel) + "'" +
                " WHERE " + DatabaseManager.ISSZMBALV3_t_rm_vip_card_id + "=" + memberCardId;
        Statement stmt = con.createStatement();//创建Statement
        stmt.executeUpdate(sql);//ResultSet类似Cursor
        row = stmt.getUpdateCount();
        stmt.close();
        if (con != null)
            con.close();
        return row;
    }

    private List<Isszmbalv3BranchInfo> collectIsszmbalv3BranchInfoList(ResultSet rs) throws Exception {
        List<Isszmbalv3BranchInfo> result = new ArrayList<Isszmbalv3BranchInfo>();
        if (rs != null) {
            while (rs.next()) {
                Isszmbalv3BranchInfo branchInfo = new Isszmbalv3BranchInfo();
                branchInfo.no = rs.getString(DatabaseManager.ISSZMBALV3_BRANCH_INFO_BRANCH_NO);
                branchInfo.name = rs.getString(DatabaseManager.ISSZMBALV3_BRANCH_INFO_BRANCH_NAME);
                branchInfo.property = rs.getString(DatabaseManager.ISSZMBALV3_BRANCH_INFO_PROPERTY);
                result.add(branchInfo);
            }
            rs.close();
        }
        return result;
    }

    public List<Isszmbalv3BranchInfo> getIsszmbalv3BranchInfo() throws Exception {
        // 加载驱动程序
        Class.forName("net.sourceforge.jtds.jdbc.Driver");
        Connection con = DriverManager.getConnection("jdbc:jtds:sqlserver://" + BarApplication.getInstance().mSqlServerHost + "/" + BarApplication.getInstance().mSqlServerDatabaseName, BarApplication.getInstance().mSqlServerUser, BarApplication.getInstance().mSqlServerPassword);
        String sql = "SELECT " + DatabaseManager.ISSZMBALV3_BRANCH_INFO_BRANCH_NO + "," +
                DatabaseManager.ISSZMBALV3_BRANCH_INFO_BRANCH_NAME + "," +
                DatabaseManager.ISSZMBALV3_BRANCH_INFO_PROPERTY +
                " FROM " + DatabaseManager.ISSZMBALV3_BRANCH_INFO_TABLE_NAME;
        Statement stmt = con.createStatement();//创建Statement
        ResultSet rs = stmt.executeQuery(sql);//ResultSet类似Cursor
        List<Isszmbalv3BranchInfo> result = collectIsszmbalv3BranchInfoList(rs);
        stmt.close();
        if (con != null)
            con.close();
        return result;
    }

    public List<Isszmbalv3PayFlow> getIsszmbalv3PayFlowByOperdate(String date, String branchNo) throws Exception {
        // 加载驱动程序
        Class.forName("net.sourceforge.jtds.jdbc.Driver");
        Connection con = DriverManager.getConnection("jdbc:jtds:sqlserver://" + BarApplication.getInstance().mSqlServerHost + "/" + BarApplication.getInstance().mSqlServerDatabaseName, BarApplication.getInstance().mSqlServerUser, BarApplication.getInstance().mSqlServerPassword);
        String sql = "";
        if (!TextUtils.isEmpty(branchNo)) {
            sql = "SELECT " + DatabaseManager.ISSZMBALV3_PAY_FLOW_FLOW_NO + "," +
                    DatabaseManager.ISSZMBALV3_BRANCH_INFO_BRANCH_NO + "," +
                    DatabaseManager.ISSZMBALV3_PAY_FLOW_OPERDATE + "," +
                    DatabaseManager.ISSZMBALV3_PAY_FLOW_SALE_AMOUNT + "," +
                    DatabaseManager.ISSZMBALV3_PAY_FLOW_PAY_AMOUNT + "," +
                    DatabaseManager.ISSZMBALV3_PAY_FLOW_CARD_ID + "," +
                    DatabaseManager.ISSZMBALV3_PAY_FLOW_PAY_WAY +
                    " FROM " + DatabaseManager.ISSZMBALV3_PAY_FLOW_TABLE_NAME + " WHERE " + DatabaseManager.ISSZMBALV3_PAY_FLOW_OPERDATE + "  > '" + date + " 00:00:00' and " + DatabaseManager.ISSZMBALV3_PAY_FLOW_OPERDATE + "<'" + date + " 23:59:59' and " + DatabaseManager.ISSZMBALV3_BRANCH_INFO_BRANCH_NO + "=" + branchNo;
        } else {
            sql = "SELECT " + DatabaseManager.ISSZMBALV3_PAY_FLOW_FLOW_NO + "," +
                    DatabaseManager.ISSZMBALV3_BRANCH_INFO_BRANCH_NO + "," +
                    DatabaseManager.ISSZMBALV3_PAY_FLOW_OPERDATE + "," +
                    DatabaseManager.ISSZMBALV3_PAY_FLOW_SALE_AMOUNT + "," +
                    DatabaseManager.ISSZMBALV3_PAY_FLOW_PAY_AMOUNT + "," +
                    DatabaseManager.ISSZMBALV3_PAY_FLOW_CARD_ID + "," +
                    DatabaseManager.ISSZMBALV3_PAY_FLOW_PAY_WAY +
                    " FROM " + DatabaseManager.ISSZMBALV3_PAY_FLOW_TABLE_NAME + " WHERE " + DatabaseManager.ISSZMBALV3_PAY_FLOW_OPERDATE + "  > '" + date + " 00:00:00' and " + DatabaseManager.ISSZMBALV3_PAY_FLOW_OPERDATE + "<'" + date + " 23:59:59'";
        }
        Statement stmt = con.createStatement();//创建Statement
        ResultSet rs = stmt.executeQuery(sql);//ResultSet类似Cursor
        List<Isszmbalv3PayFlow> result = collectIsszmbalv3PayFlowList(rs);
        stmt.close();
        if (con != null)
            con.close();
        return result;
    }

    private List<Isszmbalv3PayFlow> collectIsszmbalv3PayFlowList(ResultSet rs) throws Exception {
        List<Isszmbalv3PayFlow> result = new ArrayList<Isszmbalv3PayFlow>();
        if (rs != null) {
            while (rs.next()) {
                {
                    Isszmbalv3PayFlow payMasterInfo = new Isszmbalv3PayFlow();
                    payMasterInfo.payFlowFlow_no = rs.getString(DatabaseManager.ISSZMBALV3_PAY_FLOW_FLOW_NO);
                    payMasterInfo.payFlowBranch_no = rs.getString(DatabaseManager.ISSZMBALV3_BRANCH_INFO_BRANCH_NO);
                    payMasterInfo.payFlowOperdate = rs.getDate(DatabaseManager.ISSZMBALV3_PAY_FLOW_OPERDATE);
                    payMasterInfo.payFlowOpertime = rs.getTime(DatabaseManager.ISSZMBALV3_PAY_FLOW_OPERDATE);
                    payMasterInfo.payFlowSale_amount = rs.getFloat(DatabaseManager.ISSZMBALV3_PAY_FLOW_SALE_AMOUNT);
                    payMasterInfo.payFlowPay_amount = rs.getFloat(DatabaseManager.ISSZMBALV3_PAY_FLOW_PAY_AMOUNT);
                    payMasterInfo.payFlowCard_id = rs.getString(DatabaseManager.ISSZMBALV3_PAY_FLOW_CARD_ID);
                    payMasterInfo.payFlowPay_way = rs.getString(DatabaseManager.ISSZMBALV3_PAY_FLOW_PAY_WAY);
                    result.add(payMasterInfo);
                }
            }
            rs.close();
        }
        return result;
    }

    public long deleteIsszmbalv3PayFlowByOperdate(String deleteDate) throws Exception {
        long row = -1;
        // 加载驱动程序
        Class.forName("net.sourceforge.jtds.jdbc.Driver");
        Connection con = DriverManager.getConnection("jdbc:jtds:sqlserver://" + BarApplication.getInstance().mSqlServerHost + "/" + BarApplication.getInstance().mSqlServerDatabaseName, BarApplication.getInstance().mSqlServerUser, BarApplication.getInstance().mSqlServerPassword);
        String sql = "delete from " + DatabaseManager.ISSZMBALV3_PAY_FLOW_TABLE_NAME + " WHERE " + DatabaseManager.ISSZMBALV3_PAY_FLOW_OPERDATE + "  > '" + deleteDate + " 00:00:00' and " + DatabaseManager.ISSZMBALV3_PAY_FLOW_OPERDATE + "<'" + deleteDate + " 23:59:59'";
        Statement stmt = con.createStatement();//创建Statement
        stmt.executeUpdate(sql);//ResultSet类似Cursor
        row = stmt.getUpdateCount();
        stmt.close();
        if (con != null)
            con.close();
        return row;
    }

    public List<Isszmbalv3SaleDetailInfo> getIsszmbalv3SaleDetailByFlowno(String no, String branchNo) throws Exception {
        // 加载驱动程序
        Class.forName("net.sourceforge.jtds.jdbc.Driver");
        Connection con = DriverManager.getConnection("jdbc:jtds:sqlserver://" + BarApplication.getInstance().mSqlServerHost + "/" + BarApplication.getInstance().mSqlServerDatabaseName, BarApplication.getInstance().mSqlServerUser, BarApplication.getInstance().mSqlServerPassword);
        String sql = "";
        if (!TextUtils.isEmpty(branchNo)) {
            sql = "SELECT " + DatabaseManager.ISSZMBALV3_SALE_FLOW_FLOW_NO + "," +
                    DatabaseManager.ISSZMBALV3_BRANCH_INFO_BRANCH_NO + "," +
                    DatabaseManager.ISSZMBALV3_SALE_FLOW_OPERDATE + "," +
                    DatabaseManager.ISSZMBALV3_SALE_FLOW_ITEM_NO + "," +
                    DatabaseManager.ISSZMBALV3_SALE_FLOW_SOURCE_PRICE + "," +
                    DatabaseManager.ISSZMBALV3_SALE_FLOW_COST_PRICE + "," +
                    DatabaseManager.ISSZMBALV3_SALE_FLOW_SALE_QNTY + "," +
                    DatabaseManager.ISSZMBALV3_SALE__FLOW_CARD_ID + "," +
                    DatabaseManager.ISSZMBALV3_SALE_FLOW_SALE_MONER +
                    " FROM " + DatabaseManager.ISSZMBALV3_SALE_FLOW_TABLE_NAME + " WHERE " + DatabaseManager.ISSZMBALV3_SALE_FLOW_FLOW_NO + "  = '" + no + "'   and " + DatabaseManager.ISSZMBALV3_BRANCH_INFO_BRANCH_NO + "=" + branchNo;
        } else {
            sql = "SELECT " + DatabaseManager.ISSZMBALV3_SALE_FLOW_FLOW_NO + "," +
                    DatabaseManager.ISSZMBALV3_BRANCH_INFO_BRANCH_NO + "," +
                    DatabaseManager.ISSZMBALV3_SALE_FLOW_OPERDATE + "," +
                    DatabaseManager.ISSZMBALV3_SALE_FLOW_ITEM_NO + "," +
                    DatabaseManager.ISSZMBALV3_SALE_FLOW_SOURCE_PRICE + "," +
                    DatabaseManager.ISSZMBALV3_SALE_FLOW_COST_PRICE + "," +
                    DatabaseManager.ISSZMBALV3_SALE_FLOW_SALE_QNTY + "," +
                    DatabaseManager.ISSZMBALV3_SALE__FLOW_CARD_ID + "," +
                    DatabaseManager.ISSZMBALV3_SALE_FLOW_SALE_MONER +
                    " FROM " + DatabaseManager.ISSZMBALV3_SALE_FLOW_TABLE_NAME + " WHERE " + DatabaseManager.ISSZMBALV3_SALE_FLOW_FLOW_NO + "  = '" + no + "'";
        }
        Statement stmt = con.createStatement();//创建Statement
        ResultSet rs = stmt.executeQuery(sql);//ResultSet类似Cursor
        List<Isszmbalv3SaleDetailInfo> result = collectIsszmbalv3SaleDetailList(rs);
        stmt.close();
        if (con != null)
            con.close();
        return result;
    }

    public List<Isszmbalv3SaleDetailInfo> getIsszmbalv3SaleDetailByDate(String date, String branchNo) throws Exception {
        // 加载驱动程序
        Class.forName("net.sourceforge.jtds.jdbc.Driver");
        Connection con = DriverManager.getConnection("jdbc:jtds:sqlserver://" + BarApplication.getInstance().mSqlServerHost + "/" + BarApplication.getInstance().mSqlServerDatabaseName, BarApplication.getInstance().mSqlServerUser, BarApplication.getInstance().mSqlServerPassword);
        String sql = "";
        if (!TextUtils.isEmpty(branchNo)) {
            sql = "SELECT " + DatabaseManager.ISSZMBALV3_SALE_FLOW_FLOW_NO + "," +
                    DatabaseManager.ISSZMBALV3_BRANCH_INFO_BRANCH_NO + "," +
                    DatabaseManager.ISSZMBALV3_SALE_FLOW_OPERDATE + "," +
                    DatabaseManager.ISSZMBALV3_SALE_FLOW_ITEM_NO + "," +
                    DatabaseManager.ISSZMBALV3_SALE_FLOW_SOURCE_PRICE + "," +
                    DatabaseManager.ISSZMBALV3_SALE_FLOW_COST_PRICE + "," +
                    DatabaseManager.ISSZMBALV3_SALE_FLOW_SALE_QNTY + "," +
                    DatabaseManager.ISSZMBALV3_SALE__FLOW_CARD_ID + "," +
                    DatabaseManager.ISSZMBALV3_SALE_FLOW_SALE_MONER +
                    " FROM " + DatabaseManager.ISSZMBALV3_SALE_FLOW_TABLE_NAME + " WHERE " + DatabaseManager.ISSZMBALV3_SALE_FLOW_OPERDATE + "  > '" + date + " 00:00:00' and " + DatabaseManager.ISSZMBALV3_SALE_FLOW_OPERDATE + "<'" + date + " 23:59:59'   and " + DatabaseManager.ISSZMBALV3_BRANCH_INFO_BRANCH_NO + "=" + branchNo;
        } else {
            sql = "SELECT " + DatabaseManager.ISSZMBALV3_SALE_FLOW_FLOW_NO + "," +
                    DatabaseManager.ISSZMBALV3_BRANCH_INFO_BRANCH_NO + "," +
                    DatabaseManager.ISSZMBALV3_SALE_FLOW_OPERDATE + "," +
                    DatabaseManager.ISSZMBALV3_SALE_FLOW_ITEM_NO + "," +
                    DatabaseManager.ISSZMBALV3_SALE_FLOW_SOURCE_PRICE + "," +
                    DatabaseManager.ISSZMBALV3_SALE_FLOW_COST_PRICE + "," +
                    DatabaseManager.ISSZMBALV3_SALE_FLOW_SALE_QNTY + "," +
                    DatabaseManager.ISSZMBALV3_SALE__FLOW_CARD_ID + "," +
                    DatabaseManager.ISSZMBALV3_SALE_FLOW_SALE_MONER +
                    " FROM " + DatabaseManager.ISSZMBALV3_SALE_FLOW_TABLE_NAME + " WHERE " + DatabaseManager.ISSZMBALV3_SALE_FLOW_OPERDATE + "  > '" + date + " 00:00:00' and " + DatabaseManager.ISSZMBALV3_SALE_FLOW_OPERDATE + "<'" + date + " 23:59:59'";
        }
        Statement stmt = con.createStatement();//创建Statement
        ResultSet rs = stmt.executeQuery(sql);//ResultSet类似Cursor
        List<Isszmbalv3SaleDetailInfo> result = collectIsszmbalv3SaleDetailList(rs);
        stmt.close();
        if (con != null)
            con.close();
        return result;
    }

    public List<Isszmbalv3SaleDetailInfo> getIsszmbalv3SaleDetailByItemno(String item_no, String branchNo) throws Exception {
        // 加载驱动程序
        Class.forName("net.sourceforge.jtds.jdbc.Driver");
        Connection con = DriverManager.getConnection("jdbc:jtds:sqlserver://" + BarApplication.getInstance().mSqlServerHost + "/" + BarApplication.getInstance().mSqlServerDatabaseName, BarApplication.getInstance().mSqlServerUser, BarApplication.getInstance().mSqlServerPassword);
        String sql = "";
        if (!TextUtils.isEmpty(branchNo)) {
            sql = "SELECT " + DatabaseManager.ISSZMBALV3_SALE_FLOW_FLOW_NO + "," +
                    DatabaseManager.ISSZMBALV3_BRANCH_INFO_BRANCH_NO + "," +
                    DatabaseManager.ISSZMBALV3_SALE_FLOW_OPERDATE + "," +
                    DatabaseManager.ISSZMBALV3_SALE_FLOW_ITEM_NO + "," +
                    DatabaseManager.ISSZMBALV3_SALE_FLOW_SOURCE_PRICE + "," +
                    DatabaseManager.ISSZMBALV3_SALE_FLOW_COST_PRICE + "," +
                    DatabaseManager.ISSZMBALV3_SALE_FLOW_SALE_QNTY + "," +
                    DatabaseManager.ISSZMBALV3_SALE__FLOW_CARD_ID + "," +
                    DatabaseManager.ISSZMBALV3_SALE_FLOW_SALE_MONER +
                    " FROM " + DatabaseManager.ISSZMBALV3_SALE_FLOW_TABLE_NAME + " WHERE " + DatabaseManager.ISSZMBALV3_SALE_FLOW_ITEM_NO + "  = '" + item_no + "'   and " + DatabaseManager.ISSZMBALV3_BRANCH_INFO_BRANCH_NO + "=" + branchNo + " ORDER BY " + DatabaseManager.ISSZMBALV3_SALE_FLOW_OPERDATE + " DESC";
        } else {
            sql = "SELECT " + DatabaseManager.ISSZMBALV3_SALE_FLOW_FLOW_NO + "," +
                    DatabaseManager.ISSZMBALV3_BRANCH_INFO_BRANCH_NO + "," +
                    DatabaseManager.ISSZMBALV3_SALE_FLOW_OPERDATE + "," +
                    DatabaseManager.ISSZMBALV3_SALE_FLOW_ITEM_NO + "," +
                    DatabaseManager.ISSZMBALV3_SALE_FLOW_SOURCE_PRICE + "," +
                    DatabaseManager.ISSZMBALV3_SALE_FLOW_COST_PRICE + "," +
                    DatabaseManager.ISSZMBALV3_SALE_FLOW_SALE_QNTY + "," +
                    DatabaseManager.ISSZMBALV3_SALE__FLOW_CARD_ID + "," +
                    DatabaseManager.ISSZMBALV3_SALE_FLOW_SALE_MONER +
                    " FROM " + DatabaseManager.ISSZMBALV3_SALE_FLOW_TABLE_NAME + " WHERE " + DatabaseManager.ISSZMBALV3_SALE_FLOW_ITEM_NO + "  = '" + item_no + "' ORDER BY " + DatabaseManager.ISSZMBALV3_SALE_FLOW_OPERDATE + " DESC";
        }
        Statement stmt = con.createStatement();//创建Statement
        ResultSet rs = stmt.executeQuery(sql);//ResultSet类似Cursor
        List<Isszmbalv3SaleDetailInfo> result = collectIsszmbalv3SaleDetailList(rs);
        stmt.close();
        if (con != null)
            con.close();
        return result;
    }

    private List<Isszmbalv3SaleDetailInfo> collectIsszmbalv3SaleDetailList(ResultSet rs) throws Exception {
        List<Isszmbalv3SaleDetailInfo> result = new ArrayList<Isszmbalv3SaleDetailInfo>();
//        HashMap<String, Object> item = new HashMap<String, Object>();
//        HashMap<String, Object> item1 = new HashMap<String, Object>();
        if (rs != null) {
            while (rs.next()) {
                Isszmbalv3SaleDetailInfo saleDetailInfo = new Isszmbalv3SaleDetailInfo();
                saleDetailInfo.saleDetailflow_no = rs.getString(DatabaseManager.ISSZMBALV3_SALE_FLOW_FLOW_NO);
                saleDetailInfo.saleDetailbranch_no = rs.getString(DatabaseManager.ISSZMBALV3_BRANCH_INFO_BRANCH_NO);
                saleDetailInfo.saleDetailitem_no = rs.getString(DatabaseManager.ISSZMBALV3_SALE_FLOW_ITEM_NO);
                saleDetailInfo.saleDetailOperdate = rs.getDate(DatabaseManager.ISSZMBALV3_SALE_FLOW_OPERDATE);
                saleDetailInfo.saleDetailOpertime = rs.getTime(DatabaseManager.ISSZMBALV3_SALE_FLOW_OPERDATE);
                saleDetailInfo.saleDetailPrice = rs.getFloat(DatabaseManager.ISSZMBALV3_SALE_FLOW_SOURCE_PRICE);
                saleDetailInfo.saleDetailCost = rs.getFloat(DatabaseManager.ISSZMBALV3_SALE_FLOW_COST_PRICE);
                saleDetailInfo.saleDetailQuantity = rs.getFloat(DatabaseManager.ISSZMBALV3_SALE_FLOW_SALE_QNTY);
                saleDetailInfo.saleDetailSaleMonery = rs.getFloat(DatabaseManager.ISSZMBALV3_SALE_FLOW_SALE_MONER);
                saleDetailInfo.saleDetailcard_id = rs.getString(DatabaseManager.ISSZMBALV3_SALE__FLOW_CARD_ID);
//                // 跳过重复的SaleDetail数据
//                if ((String) item.get(saleDetailInfo.saleDetailOperdate.toString()) != null
//                        && (String) item1.get(saleDetailInfo.saleDetailOpertime.toString()) != null) {
//                    if (((String) item.get(saleDetailInfo.saleDetailOperdate.toString())).equals(saleDetailInfo.saleDetailOperdate.toString())
//                            && ((String) item1.get(saleDetailInfo.saleDetailOpertime.toString())).equals(saleDetailInfo.saleDetailOpertime.toString())) {
//                        continue;
//                    }
//                }
//
//                item.put(saleDetailInfo.saleDetailOperdate.toString(), saleDetailInfo.saleDetailOperdate.toString());
//                item1.put(saleDetailInfo.saleDetailOpertime.toString(), saleDetailInfo.saleDetailOpertime.toString());

                result.add(saleDetailInfo);
            }
            rs.close();
        }
        return result;
    }


    public List<Isszmbalv3MemberPointInfo> getIsszmbalv3MemberPointByMemberId(String memberCardId) throws Exception {
        // 加载驱动程序
        Class.forName("net.sourceforge.jtds.jdbc.Driver");
        Connection con = DriverManager.getConnection("jdbc:jtds:sqlserver://" + BarApplication.getInstance().mSqlServerHost + "/" + BarApplication.getInstance().mSqlServerDatabaseName, BarApplication.getInstance().mSqlServerUser, BarApplication.getInstance().mSqlServerPassword);
        String sql = "SELECT * FROM " + DatabaseManager.ISSZMBALV3_t_rm_vip_acclist_TABLE_NAME + " WHERE " + DatabaseManager.ISSZMBALV3_t_rm_vip_acclist_card_id + "=" + memberCardId;
        Statement stmt = con.createStatement();//创建Statement
        ResultSet rs = stmt.executeQuery(sql);//ResultSet类似Cursor
        List<Isszmbalv3MemberPointInfo> result = collectIsszmbalv3MemberPointList(rs);
        stmt.close();
        if (con != null)
            con.close();
        return result;
    }

    private List<Isszmbalv3MemberPointInfo> collectIsszmbalv3MemberPointList(ResultSet rs) throws Exception {
        List<Isszmbalv3MemberPointInfo> result = new ArrayList<Isszmbalv3MemberPointInfo>();
        if (rs != null) {
            while (rs.next()) {
                Isszmbalv3MemberPointInfo isszmbalv3MemberPointInfo = new Isszmbalv3MemberPointInfo();
                isszmbalv3MemberPointInfo.memberPointFlowno = rs.getString(DatabaseManager.ISSZMBALV3_t_rm_vip_acclist_flow_no);
                isszmbalv3MemberPointInfo.memberPointCardid = rs.getString(DatabaseManager.ISSZMBALV3_t_rm_vip_acclist_card_id);
                isszmbalv3MemberPointInfo.memberPointOperdate = rs.getDate(DatabaseManager.ISSZMBALV3_t_rm_vip_acclist_ope_date);
                isszmbalv3MemberPointInfo.memberPointOpertime = rs.getTime(DatabaseManager.ISSZMBALV3_t_rm_vip_acclist_ope_date);
                isszmbalv3MemberPointInfo.memberPointConsumAmt = rs.getFloat(DatabaseManager.ISSZMBALV3_t_rm_vip_acclist_consum_amt);
                result.add(isszmbalv3MemberPointInfo);
            }
            rs.close();
        }
        return result;
    }
}
